Hi guys,
Any idea on why is the sum of the percentages in the Graph different from the sum of the Card?
5,20 + 8,20 + 7,95 + 7,00 + 7,45 + 6,16 + 4,69 = 46,65% at total or 46,65 / 7 = 6,66% on average.
But in the Card...
The average also not the same...
The DAX measure:
Rotatividade Geral =
VAR noApprentice =
FILTER (
KEEPFILTERS ( VALUES ( CARGO[DESC_CARGO] ) ),
NOT ( SEARCH ( "APPRENTICE", CARGO[DESC_CARGO], 1, 0 ) = 1 )
)
VAR movimentacao =
SUMX (
Medidas,
CALCULATE ( [Admissions of the period], SemAp )
+ CALCULATE ( [Resigns of the period], SemAp )
)
VAR efetivoMedio =
SUMX (
measures,
CALCULATE ( [Headcount beggining of the month], SemAp )
+ CALCULATE ( [Headcount without Absence], SemAp )
)
VAR turnover =
DIVIDE ( movimentacao, efetivoMedio )
RETURN
turnover
Any thoughts?
1 ACCEPTED SOLUTION
Hi, here's the DAX measure that solves the problem:
Turnover = SUMX(
VALUES(DATA[Mês]),
CALCULATE(SUMX(Medidas,[Rotatividade Geral]))
)
What was the problem? When filtering more than one month in the analysis, Power BI was calculating the turnover based on the grand total of all months filtered. What this measure does, is that it sum the percentages for every month, row by row, not by the total.
What was the problem? When filtering more than one month in the analysis, Power BI was calculating the turnover based on the grand total of all months filtered. What this measure does, is that it sum the percentages for every month, row by row, not by the total.
Hi, here's the DAX measure that solves the problem:
Turnover = SUMX(
VALUES(DATA[Mês]),
CALCULATE(SUMX(Medidas,[Rotatividade Geral]))
)
What was the problem? When filtering more than one month in the analysis, Power BI was calculating the turnover based on the grand total of all months filtered. What this measure does, is that it sum the percentages for every month, row by row, not by the total.
What was the problem? When filtering more than one month in the analysis, Power BI was calculating the turnover based on the grand total of all months filtered. What this measure does, is that it sum the percentages for every month, row by row, not by the total.
I've done the same calculation in a new file and the number were correct. So maybe the problem is inside my PBI. I've tried everything, from changing the measure, deleting all others visuals, removing page and report filters...
The grand total always results in a number different from the sum of the percentages.
What's confusing is that the measure isn't all that complicated, and for all the tests made, the numbers of headcount and the numbers of admissions and resigns results in the correct percentage for the month.
As always, these calculated results are a nightmare of every analyst that uses PBI.
Data Sample:
| MonthNo | Month | Turnover | Admissions | Resigns | Headcount without absence | Headcount Beggining of the Month |
| 1 | January | 5,20% | 235 | 254 | 4695 | 4698 |
| 2 | February | 8,20% | 450 | 328 | 4776 | 4704 |
| 3 | March | 7,95% | 431 | 336 | 4847 | 4800 |
| 4 | April | 7,00% | 342 | 335 | 4826 | 4841 |
| 5 | May | 7,45% | 393 | 328 | 4871 | 4808 |
| 6 | June | 6,16% | 319 | 281 | 4887 | 4856 |
| 7 | July | 4,79% | 244 | 227 | 4916 | 4911 |
You can try the following measures.
Sum_Rotatividade =
SUMX ( ALLSELECTED ( table ), [Rotatividade Geral] )
Average_Rotatividade =
DIVIDE (
[Sum_Rotatividade],
CALCULATE ( DISTINCTCOUNT ( table[Month] ), ALLSELECTED ( table ) )
)
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.
