Hi, I am trying to find a way that I can format my values of the pivot table based on the column they are under. The primary problem for me is that I have integers like counts, currency values, percentages all under the same column in my dataset. Sample of my dataset looks like below.
And currently the pivot table in my dashboard looks like this.
| Profit Percentage | Total Value | |||||
|---|---|---|---|---|---|---|
| Item | current | comparison | delta | current | comparison | delta |
| Item-1 | 40.2 | 40.11 | 0.1 | 31,513,261.29 | 40,843,593.51 | -9,330,332.21 |
| Item-2 | 40.99 | 41.23 | -0.24 | 34,327,840.32 | 39,550,982.97 | -5,223,142.64 |
| Item-3 | 40.96 | 40.86 | 0.1 | 37,998,175.99 | 47,103,787.03 | -9,105,611.03 |
| Item-4 | 41.81 | 42.18 | -0.36 | 12,551,208.56 | 17,776,975.11 | -5,225,766.54 |
| Item-5 | 38.91 | 37.08 | 1.83 | 3,692,078.11 | 2,570,127.86 | 1,121,950.25 |
| Item-6 | 0 | 0 | 0 | 0 | 0 | 0 |
I’m trying to achieve a pivot table that has proper formatting for the currency vs percentages vs counts based on the column(metric) they are under. Is there a way I could achieve this?
So far, I was trying to apply currency formatting to the measure and it messes-up the percentages and counts. I also tried to format them in the direct query that is populating my dataset which made those into strings and that did not help as they can no longer be added as values to the pivot table.
