Blowers
Blowers

Reputation: 75

Creating a Measure to calculate Percentage in Tableau

Normally in Tableau to calculate % I create a measure and use the below logic;

SUM ([MEASURE_1])/ SUM([MEASURE_2])

However i am trying to get the % when using a measure and an already aggregated measure and I'm getting the below error;

Argument to SUM (an aggregate function is already an aggregation, and cannot be further aggregated.

My percentage is to calculated the % difference between a policy count from table 1 which is a measure. and measure 2 which I had to create to get the count of policies from table 2 as follows; Count([Policy]). I'm using SQL Server database.

Any ideas how i can resolve this issue?

Thanks!

Measure 1 is a count from table 1, the data looks like the following;

enter image description here

Measure 2 is from table 2, I am creating a count in tableau of the policy number, the data looks like the below;

enter image description here

The 2 tables join on agent number

Table 1 structure;

   [UNIQUEPOL_CNT]
  ,[UNIQUEAGT_CNT]
  ,[VECHICLE_TXT]
  ,[VEHICLE_DESC]
  ,[VEHICLE_CD]
  ,[CODE_DESC]
  ,[POLICY_ID]
  ,[POLICY_NBR]
  ,[STATE_CD]
  ,[AGENT_NBR]
  ,[AGENT_NM]
  ,[PROP_ID]
  ,[WRITTEN_DT]
  ,[PURCHASE_DT]
  ,[SUSP_IND]
  ,[SUSPEND_DT]
  ,[BIND_ID]
  ,[INSURED_NM]
  ,[CHANGEDBY_ID]

table 2 structure;
 
   [REPORT_DT]
  ,[AGENT_NBR]
  ,[TRR_TOTALPIF_CNT]
  ,[TRR_TOTALPON_CNT]
  ,[TRR_TOTALREV_CNT]
  ,[TRR_TOTALERR_CNT]

This is the invalid output...

enter image description here

Upvotes: 0

Views: 1353

Answers (1)

AnilGoyal
AnilGoyal

Reputation: 26218

UPDATED

I created two datasets to demonstrate you a solution

dataset1

enter image description here

dataset 2

enter image description here

Connecting both datasets in Tableau.

enter image description here

I built a view in tableau as (Note policies field blue color as it has been converted to dimension)

enter image description here

Now calculate a field as

ATTR({FIXED [Agent code]: count([Policy no])}/[Policies])

Adding this to viz gives me

enter image description here

wherein desired percentages are displayed.

Note- since the tables have one-to-many relationships fixed statement in respect of measure on lesser side of relationship will also result in same result.

ATTR({FIXED [Agent code]: count([Policy no])}/
{FIXED [Agent code]: AVG([Policies])})

Upvotes: 1

Related Questions