Incorrect summation of a measure

[sidh86]

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.

Thanks in advance,

Sid

 

[Ashish_Mathur · Accepted solution]

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.

[sidh86]

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!)

[Ashish_Mathur]

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

[Greg_Deckler]

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