Forum Discussion
Percentage based on subcategory
Hi,
Except Group1 rest of all the groups it is calculating correctly. For Group1 it is taking the Total of all months instead of respective months. Request for help to resolve the issue. I am struggling for last one week
eg. 4,95,00,000/5,85,00,000 = 82.93 %
but it should be
4,95,00,000/5,96,90,000 =84.62 (Is correct)
Total Revenue % = SUM(ABP[Revenue])/ CALCULATE(SUM(ABP[Revenue]), ALLEXCEPT(ABP, ABP[Group]), ABP[Group]="Group 1")
| April | May | June | Total | ||||||
| Group | Region | Revenue | % | Revenue | % | Revenue | % | Revenue | % |
| Group1 | India | 4,95,00,000.00 | 82.93 | 5,00,000.00 | 0.01 | 40000 | 0.00 | 5,00,40,000.00 | 83.833138 |
| Group1 | Africa | 90,00,000.00 | 15.08 | 6,00,000.00 | 0.01 | 50000 | 0.00 | 96,50,000.00 | 16.166862 |
| Total (a) | 5,85,00,000.00 | 98.01 | 11,00,000.00 | 0.02 | 90000 | 0.00 | 5,96,90,000.00 | 100 | |
| Group2 | Middle East | 90,00,000.00 | 15.38 | 70,000.00 | 0.06 | 80000 | 0.89 | 91,50,000.00 | 15.329201 |
| Group2 | UAE | 80,00,000.00 | 13.68 | 80,000.00 | 0.07 | 70000 | 0.78 | 81,50,000.00 | 13.653878 |
| Total | 1,70,00,000.00 | 29.06 | 1,50,000.00 | 0.14 | 150000 | 1.67 | 1,73,00,000.00 | 28.983079 | |
| Group3 | America | 27,00,00,000.00 | 461.54 | 9,00,000.00 | 0.82 | 90000 | 1.00 | 27,09,90,000.00 | 453.99564 |
| Group3 | Russia | 11,25,00,000.00 | 192.31 | 10,000.00 | 0.01 | 100000 | 1.11 | 11,26,10,000.00 | 188.65807 |
| Total | 38,25,00,000.00 | 653.85 | 9,10,000.00 | 0.83 | 190000 | 2.11 | 38,36,00,000.00 | 642.65371 |
I hope this will finally get you the result you are looking for. To bring back the month context, you can add a Values clause to the calculate. Please try this for the reg2 variable
reg2 = CALCULATE(SUM(ABP[Revenue]), All(ABP), ABP[Group]="Group 1", Values(Date[Month])) //use the table column for month used in your visual
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat