Search code examples
powerbirowpercentagedivisiondivide

Divide an amount by each row total to calculate percentage

I have a doubt and I don't know how to solve it.

I have this information and I want to calculate the percentage on every row of sexes, for example in Headcount for Men should be (80.294/175.230)*100 in Voluntary Leaves (3.214/6.521)*100… and I am doing wrong because I am doing it always dividing by the total Headcount which is 175.230.

foto1

What I have is:

2.MenActPer = 
CALCULATE(SUM(TablaUnica[Men]), 
FILTER(ALL('Date'[Date]), 'Date'[Date] >= DATE(YEAR(MAX('Date'[Date])), MONTH(MAX('Date'[Date])) - 6, DAY(MAX('Date'[Date])))))
/ 
CALCULATE(SUM(TablaUnica[Total]),
FILTER(ALLSELECTED(TablaUnica), TablaUnica[Item]="Headcount"),
FILTER(ALL('Date'[Date]), 'Date'[Date] >= DATE(YEAR(MAX('Date'[Date])), MONTH(MAX('Date'[Date])) - 6, DAY(MAX('Date'[Date])))))

How should I change the divider to divide by every total row?

Solution

  • Change your calculation a little bit:

    TablaUnica[Item]="Headcount" to TablaUnica[Item] = SELECTEDVALUE(TablaUnica[Item])

    2.MenActPer = 
CALCULATE(SUM(TablaUnica[Men]), 
FILTER(ALL('Date'[Date]), 'Date'[Date] >= DATE(YEAR(MAX('Date'[Date])), MONTH(MAX('Date'[Date])) - 6, DAY(MAX('Date'[Date])))))
/ 
CALCULATE(SUM(TablaUnica[Total]),
FILTER(ALLSELECTED(TablaUnica), TablaUnica[Item]= SELECTEDVALUE(TablaUnica[Item])),
FILTER(ALL('Date'[Date]), 'Date'[Date] >= DATE(YEAR(MAX('Date'[Date])), MONTH(MAX('Date'[Date])) - 6, DAY(MAX('Date'[Date])))))