Creating a Measure to calculate Percentage in Tableau

Question

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...

I have update my question with the column details from the two different tables, thanks! — Blowers
Do you want 58/12 as output which is sum(measure1) =58 and count of measure2 = 12?? I am not getting this — AnilGoyal
yes, i would like to get 58/12 as output which is sum(measure1) =58 and count of measure2 = 12 — Blowers

AnilGoyal AnilGoyal · Accepted Answer · 2021-02-03T09:38:13

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])})

Creating a Measure to calculate Percentage in Tableau

1 Answers