Weird behaviour with SUMX and date filters

[icbd]

I'm having a very weird issue with SUMX and date filters.

The current formula I am using is as follows:

RunTimeTest2 = SUMX('Date', MAX(DataPartCell1[ShiftHour]) - MIN(DataPartCell1[ShiftHour]))

 Where 'Date' is a calendar table marked as such, and the [ShiftHour] is the hour converted as decimal.

What I am trying to achieve is somewhat obvious from the formula, I want to subtract the max value and the minimum value for each selected date.

This works perfect for a single day, but as soon as I select more than one day in my date filter, SUMX calculates the first day, and multiplies that by the number of days selected, ignoring all subsequent days.

When I put the measure into a matrix, it show up correctly for each date, with the total being completely off.

Here is an example of what I mean:

The values shown (22.92 and 13.17) are correct, the total is not. I have 2 days selected so the total is 2022-01-27*2 = 22.92*2 = 45.83

How can I fix this? I'm kind of stumped here.

[ValtteriN]

Hi,

This is a common issue and it has to do with the way totals work on visualizations. I recommend cheking this post by SQLBI to understand what is going on: https://www.sqlbi.com/articles/summing-values-for-the-total/

[icbd · Accepted solution]

I was able to solve this following this guide:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Using two measures, the first one being:

RunTimeTest2 = SUMX('Date', MAX(DataPartCell1[ShiftHour]) - MIN(DataPartCell1[ShiftHour]))

The second one being:

ActualRuntime = 
VAR __table = SUMMARIZE('Date',[Date],"__value",[RunTimeTest2])
RETURN
IF(HASONEVALUE('Date'[Date]),[RunTimeTest2],SUMX(__table,[__value]))