Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
PauloRicardo
Helper I
Helper I

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

Hi guys, 

Any idea on why is the sum of the percentages in the Graph different from the sum of the Card?

PauloRicardo_0-1721763004605.png


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

PauloRicardo_1-1721763084826.png


The average also not the same...

PauloRicardo_2-1721763110451.png


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
PauloRicardo
Helper I
Helper I

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.

View solution in original post

5 REPLIES 5
PauloRicardo
Helper I
Helper I

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
Most Valuable Professional
Most Valuable Professional

Can you share somedata ?


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

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:

MonthNoMonthTurnoverAdmissionsResignsHeadcount without absenceHeadcount Beggining of the Month
1January5,20%23525446954698
2February8,20%45032847764704
3March7,95%43133648474800
4April7,00%34233548264841
5May7,45%39332848714808
6June6,16%31928148874856
7July4,79%24422749164911

Hi,

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.