cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Incorrect measure totals despite using Summarize workaround

I'm struggling with measure totals despite using recommended ways around the problem. I need to total the hours logged across selected staff members and date range.

My data looks like this  (some values removed).

My measure is:

 

StandardHours =
VAR StandardHours =COUNTROWS(DISTINCT('Timesheet Data'[Date]))*7.5
VAR StandardHoursTotal = SUMMARIZE('Timesheet Data','Timesheet Data'[Staff Name],"StddHrs", StandardHours)
RETURN
IF(
HASONEFILTER('Timesheet Data'[Staff Name]),
StandardHours,
SUMX(StandardHoursTotal,[StddHrs])
)

 

Output is coming out at:

Staff NameStandard Hours
A37.5
B37.5
C22.5
D

30.0

Total150

 

The total should be 127.5. The individual staff numbers are correct.

 

It seems DAX is just adding 37.5 (i.e. standard working week in hours) for each member of staff, regardless of how many actual days are logged in the fact table. I'm counting the rows using DISTINCT to find the number of days logged in that week.

The table is filtered on a slicer to "last 1 weeks (calendar)" but it doesn't make any difference if I remove the slicer- the total is still incorrect.

Any help much appreciated. Where am I going wrong?

2 REPLIES 2
Highlighted
Super User IX
Super User IX

Re: Incorrect measure totals despite using Summarize workaround

@catnapwat - I usually do this as 2 separate measures as here: This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Incorrect measure totals despite using Summarize workaround

@Greg_DecklerThanks- I actually have both of your useful guides open at the moment, but I still couldn't figure it out.

 

A user on Reddit has cracked it though- and noted that the variable didn't have any filtering applied as the summary table was being built. I had assumed the filtering was based on where the variable was used rather than where it is defined, which was incorrect. So they suggested I recalculate the variable inside the Summarize which filters it correctly:

 

StandardHours = 
VAR StandardHours =COUNTROWS(DISTINCT('Timesheet Data'[Date]))*7.5
VAR StandardHoursTotal = SUMMARIZE('Timesheet Data','Timesheet Data'[Staff Name],"StddHrs", 
    SUMX(
        VALUES('Timesheet Data'[Staff Name]),
        COUNTROWS(DISTINCT('Timesheet Data'[Date]))*7.5)
        )
RETURN
IF(
    HASONEFILTER('Timesheet Data'[Staff Name]),
    StandardHours,
    SUMX(StandardHoursTotal,[StddHrs])
) 

 Now I just have a bunch more measures to correct...

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors