Forum Discussion
Dynamic % of row total
Hi All,
I have a Matrix, where Country and city as column and Business group(BG) and function as rows. I want to calculate the percentage of row total for country wise and If I expand to city then By city wise. Simillarly first @ BG wise and If I expand my rows to function, Then function wise.
Please seethe screen shot for refeence:
For example under COO for ops I should get another two column, TR (%)_People = 42% & NY (%)_People = 58%. Basically for each row it should take row total as denominator and given value as numerator for percenatge calculation.
Also when I contract for city to country Then above calculation should also change.
Data source Sceen shot:
| BG | Function | Country | City | Number of People |
| COO | ops | US | NY | 4 |
| COO | ops | CAN | TR | 3 |
| COO | Service | US | CL | 6 |
| HR | ops | US | NY | 1 |
| HR | Payment | CAN | ON | 3 |
| Finance | ops | US | MN | 3 |
| Finance | Service | CAN | TR | 6 |
| Finance | Service | CAN | TR | 8 |
| Finance | Payment | US | SF | 2 |
Thanks in Advance!!
Hi Bali21
the following measures can be considered
Measure = SWITCH(TRUE(),ISINSCOPE('Table'[Function])&&ISINSCOPE('Table'[City]),DIVIDE(SUM('Table'[Number of People]),CALCULATE(SUM('Table'[Number of People]),ALLEXCEPT('Table','Table'[BG],'Table'[Country ],'Table'[Function]))),ISINSCOPE('Table'[Function]),DIVIDE(SUM('Table'[Number of People]),CALCULATE(SUM('Table'[Number of People]),ALLEXCEPT('Table','Table'[BG],'Table'[Function]))),ISINSCOPE('Table'[City]),DIVIDE(SUM('Table'[Number of People]),CALCULATE(SUM('Table'[Number of People]),ALLEXCEPT('Table','Table'[BG],'Table'[Country ]))),SUM('Table'[Number of People]))Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi Bali21
the following measures can be considered
Measure = SWITCH(TRUE(),ISINSCOPE('Table'[Function])&&ISINSCOPE('Table'[City]),DIVIDE(SUM('Table'[Number of People]),CALCULATE(SUM('Table'[Number of People]),ALLEXCEPT('Table','Table'[BG],'Table'[Country ],'Table'[Function]))),ISINSCOPE('Table'[Function]),DIVIDE(SUM('Table'[Number of People]),CALCULATE(SUM('Table'[Number of People]),ALLEXCEPT('Table','Table'[BG],'Table'[Function]))),ISINSCOPE('Table'[City]),DIVIDE(SUM('Table'[Number of People]),CALCULATE(SUM('Table'[Number of People]),ALLEXCEPT('Table','Table'[BG],'Table'[Country ]))),SUM('Table'[Number of People]))Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.