Percent of a Column Value against the Total

Question & Answer
1

I have a Pivot table with 3 rows, 2 columns, and two values. 1st value field is a distinct count. I need the 2nd value field to be the % of the first value for the column against the total value.

image
image688×541 36 KB

So my top level column, I would see two values under it, the total volume of Cases against the State Group, and a percentage of that volume against the total column. Once the State Group is expanded to show State, I would then see the same Volume & % field for each individual state compared to the total.
Everything I have found so far seems to use vertical table calculations as opposed to horizontal?

Any questions, issues, or further information and just let me know, many thanks.
Riley

2

Please look at percentOfTotal

1 Like
3

Hi @Srikanth_Baheti,

Thank you for your suggestion, I have used it before and tried using it again. Maybe I’m using/implementing it incorrectly but it’s not giving me what I need.
I have tried a couple of variations: percentOfTotal(count(state), [syscreated]), percentOfTotal(count(state))
For example using Sep 20, 2023 row from original screenshot. Under ‘Case Rejected’ I have 2, I want the column next to it to display 5.41% (2/37). Then ‘Case Cancelled’ would be 2.7% (1/37) and so on. But also if ‘Case Closed’ was collapsed it would show 8.11% (3/37) which is ‘Case Rejected’ + 'Case Cancelled.
Any further questions or information required and just let me know, many thanks.
Riley

4

I have found the solution for this which is adding a table calculation function into the pivot table. Previously couldn’t be done as my other value metric is a distinct count which is not supported for percent of total calculation. So I had to find a solution to change my distinct count to a count.