Totals not showing correctly for a dax measure

[Powereports]

Hi everyone,

 I have the below dax measure as below which works perfect at the employee level drill down highted in yellow in Screenshot: 1, but as we drill a level up i.e ( RPN infusion in screenshot below ) the totals don't show up correctly as highted in red in screenshot below. I tried using SUMX as well but it doesn't work well.

Please let me know if I am missing something.

Dax measure:

Screenshot 1:

Thanks in advance ,

Powerreports

[PaulOlding · Accepted solution]

Hi @Powereports 

Without sample (raw) data this will be difficult to diagnose.  Does this give you what you want?

Average Hours Per Period =
CALCULATE(
    AVERAGEX(
        SUMMARIZE(Utilization, Utilization[EMPL_ID], Datetable[Date]),
        SUM(Utilization[UTILIZATION])
        ),
    USERELATIONSHIP(Datetable[Date], Utilization[check Date])
    )

[Powereports]

Hi Paul,

 Here is the raw sample data below:

To explain i am using a matrix visual and have three levels of Heirarchy : MonthYear,Category and Employee.

So i want to know  average hours per month, per category and employee as and when i drill down.

My problem is the the dax below gives correct result at the lowest level of drill down i.e employee level but one level up i.e category the results are screwed.

For example for the screenshot below at category: (RN Infusion)  the result for average hours should be 25 and not 8.80 as highlighted in red.

Dax used for calculation :

Screenshot : 

Mon-Year	Role	Staff Name	PayCheck Date	Staff Utilized	Utilization(Hours Worked per pay period)
Sep-21	RN Infusion	Haley	9/10/2021	1	7
Sep-21	RN Infusion	Kimberly	9/3/2021	1	28
Sep-21	RN Infusion	Kimberly	9/17/2021	1	31
Sep-21	RN Infusion	Melissa	9/24/2021	1	23
Sep-21	RN Infusion	Nick	9/3/2021	1	85
Sep-21	RN Infusion	Nick	9/17/2021	1	1
Sep-21	RN Infusion	Rowena	9/3/2021	1	1

[Anonymous · Accepted solution]

Hi @Powereports ,

[Utilized Utilization(Hours Worked per pay period)] field selects Average.

Is this the result you want? If not, please provide your expected result.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulOlding · Accepted solution]

Hi @Powereports 

Without sample (raw) data this will be difficult to diagnose.  Does this give you what you want?

Average Hours Per Period =
CALCULATE(
    AVERAGEX(
        SUMMARIZE(Utilization, Utilization[EMPL_ID], Datetable[Date]),
        SUM(Utilization[UTILIZATION])
        ),
    USERELATIONSHIP(Datetable[Date], Utilization[check Date])
    )