Dynamic % of row total

[Bali21]

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!!

[Anonymous · Accepted solution]

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.