Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to convert Values in a Pivot table into Percentages.
The expression used to get the counts / values is:
> Count(distinct ID)
I tried using the below formula to get the percentages, but the effort is in vain.
> Count(distinct ID) / Count( total distinct ID)
> Count(distinct ID) / Count({$<Date1>} total distinct ID))
> Count(distinct ID) / Count({$<Date2>} total distinct ID))
> Count(distinct ID) / Count({$<Date1>} distinct ID))
> Count(distinct ID) / Count({$<Date1>} distinct ID))
The sample data appears like this:
Table 1:
Date2 | |||||
Date1 | 2016May | 2016Jun | 2016Jul | 2016Aug | |
Totals | 1570 | 200 | 330 | 560 | 380 |
2016May | 560 | 200 | 150 | 90 | 120 |
2016Jun | 430 | - | 180 | 220 | 30 |
2016Jul | 330 | - | - | 250 | 80 |
2016Aug | 250 | - | - | - | 150 |
Table 2: Required
Date2 | |||||
Date1 | 2016May | 2016Jun | 2016Jul | 2016Aug | |
Totals | 1570 | ||||
2016May | 560 | 36% | 27% | 16% | 21% |
2016Jun | 430 | - | 42% | 51% | 7% |
2016Jul | 330 | - | - | 76% | 24% |
2016Aug | 250 | - | - | - | 60% |
Kindly suggest the appropriate method to get the results.
Regards,
Mrutyunjaya
Try Count(distinct ID) / Count(TOTAL <Date1> distinct ID)
Hi Gysbert,
The objective is not met here again. The formula divides each value by Total(row totals).
viz; 560 / 1570
200 / 1570
150 / 1570
But, the desired value is,
viz; 560 / 560
200 / 560
150 / 560
Reg,
Mrutyunjaya
How about removing the show total under dimension options and using the same formula
No changes. The result is same.
Hi Mrutyunjaya,
Could you please let me know if the scenario of calculating % has been achieved in QS pivot table? I am working on the same scenario, but still couldn't crack the solution to it.
Thanks in advance.