Reputation: 75
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;
Measure 2 is from table 2, I am creating a count in tableau of the policy number, the data looks like the below;
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...
Upvotes: 0
Views: 1353
Reputation: 26218
UPDATED
I created two datasets to demonstrate you a solution
dataset1
dataset 2
Connecting both datasets in Tableau.
I built a view in tableau as (Note policies
field blue color as it has been converted to dimension)
Now calculate a field as
ATTR({FIXED [Agent code]: count([Policy no])}/[Policies])
Adding this to viz gives me
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