How to achieve 100% in Subtotals when having 3 or more Dimensions in the Pivot table

[chabbils]

Hi all,

I have 2 dimensions in the first visual and am able to get 100% in Subtotals using Table Calculation as “Percent of Total” and Calcuate as “Group Down” but it’s not working in the second visual which has 3 dimensions. I tried all “Calculate as” options i.e. Table Down, Group Down, Group Down across etc. but am not able to get 100% in Subtotals. Can you please tell me how can I achieve it?

Thanks

[Thomas]

Hi @chabbils,
You can achieve your desired behavior by using the percentOfTotal function in a calculated field.
Simply add all dimensions that you use in your rows and columns, except the lowest level of the rows, into the partition field of the function. The result should look similar to the following expression:

percentOfTotal(sum({Number of Customer Column}),[{Gifting Experience},{Age Band}, {Date Column}])

To get it now formatted as percentage , you simply adjust the Show as property of the value to Percent

The final result will look then similar (as I used different dimensions) to the following:

Did this answer your question? If so, please help the community out by marking this answer as "Solution!