cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

New Member

## Incorrect summation of a measure

Hi everyone,

Relatively new Power BI user here. I have seen similar threads on the forum but for the life of me can't apply it to my situation.

I am trying to create a measure "Hours Available", which takes my hours available in a work day from my Calendar table (8 hours a day for every weekday), and multiplies it by the count of my users in a different table for a given time period. My formula is as follows:

Hours Available =
SUM('Date'[Hours]) * DISTINCTCOUNT('Avaza_TimesheetEntry_2018 (2)'[Assigned User])

What I'm finding is that the formula evaluates correctly for each row, but the summation is off (see example below - the sum of Hours Available should actually be 5,720). The problem seems to be that the Distinct Count of users for the summation row is counting the max number of users in the table, rather than taking the sum of each of the evaluated rows. This produces a difference because some users were not employees during certain weeks (e.g. their start dates were in Week 3 rather than Week 1). Is there a way for the summation to reflect the sum of each evaluated row, rather than to recalculate the formula with the count of users in the table across all weeks?

Based on reading through some other threads, I'm wondering if it's the use of SUM vs. SUMX, but if that's the case, I can't seem to figure out the correct syntax.

Sid

1 ACCEPTED SOLUTION
Super User

Hi,

Try this measure

=SUMX(SUMMARIZE(VALUES(Date[Week]),[Week],"ABCD",SUM('Date'[Hours]) * DISTINCTCOUNT('Avaza_TimesheetEntry_2018 (2)'[Assigned User])),[ABCD])

I have assumed that the Week columnin your visual is from the Date Table.  If not, then please change the reference in my formula.  If this formula does not give the correct result, then share the link from where i can download your file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
4 REPLIES 4
Super User

Hi,

Try this measure

=SUMX(SUMMARIZE(VALUES(Date[Week]),[Week],"ABCD",SUM('Date'[Hours]) * DISTINCTCOUNT('Avaza_TimesheetEntry_2018 (2)'[Assigned User])),[ABCD])

I have assumed that the Week columnin your visual is from the Date Table.  If not, then please change the reference in my formula.  If this formula does not give the correct result, then share the link from where i can download your file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
New Member

This worked! Thanks Ashish! If you're able to, could you walk me through the logic of your formula? I don't quite follow along (still a newbie!)

Super User

You are welcome.  I'd request you to please read up on the SUMMARIZE function.  If you still have doubts, post back.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...