How to calculate percentage of grand total using exclude?

Question

I have sales data for each country which is split into different customer groups.

                     AT    CH     De    FL     Total

Customer Group A    100    72    488    16       676 
Customer Group B     53    62    280     3       398
Customer Group C     12     9    122     0       143

Total               165   143    890    19      1217

Now I would like to calculate the share of a particular customer group in a single country of the grand total of sales, e.g. for Customer Group B in DE I should get:

280/1217*100 = 23%

First I tried SUM([Sales]) / TOTAL(SUM([Sales])) to get percentage of sales in each country. Then I wanted to use customer group filter to see the share of a particular customer group. However, if I do this, the percentage is not correct, e.g. for Customer Group B in DE I get this:

280/398*100 = 70.3%

Using EXCLUDE would solve my problem... However, I am not really sure how to do this... I tried several options and none of these have worked...

{EXCLUDE [Customer Group]: SUM([Sales]) / TOTAL(SUM([Sales]))}

{EXCLUDE [Customer Group]: SUM([Sales])} / {EXCLUDE [Customer Group]: TOTAL(SUM([Sales]))}

Since I am new to tableau, I have no idea what the problem could be... Does anyone know how to do this?

Jose Cherian Jose Cherian · Accepted Answer · 2018-11-12T21:03:06

Exclude/Include LODs are calculated only after the Dimension Filters, so you need to use FIXED here. Also note that dimensions in filter shelf does not add to the level of detail even though it will affect the total.

you can use below calculation:

{FIXED [Customer Group],[Country]:SUM([Sales])}/{SUM([Sales])}

How to calculate percentage of grand total using exclude?

1 Answers