Any idea on why the average on the Graph is different from the Card?

[PauloRicardo]

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?

[PauloRicardo · 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.

[AmiraBedh]

Can you share somedata ?

[PauloRicardo]

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

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[v-xinruzhu-msft]

Hi @PauloRicardo 

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.