Hi,
I have several measures that make up my final measure. The base/first level measures include calculations from multiple tables, the second/last level measure then calculates the final values to be summed. My problem arises at the last level measure where I want to exclude negative results from the first level measures. While at row level the negative result is excluded, it is included in the Total which I can’t seem to fix.
[Measure 3] = [Measure 2] - [Measure 1]
Desired outcome: if [Measure 2] - [Measure 1] is negative, this should be excluded from the Total summation.
The issue arises in Feb month column, ID row.
I’ve done a google search but most solutions are for data within a single table.
Really appreciate your help on this, thank you!
dna enterprise.pbix (285.0 KB)
Answer:
This is one way to fix it:
Measure 3 FIXED = SUMX( VALUES('DIMS_Country 2023'[Country]), VAR _diff = [Measure 2]-[Measure 1] RETURN IF(_diff>=0,_diff))
In your version, the calculation at each row is correct, but for the total, DAX does not iterate over each row, it only looks at the total level. So, it sees Measure2 (854) and Measure1 (512), their difference is not negative, so it shows it: 854-512 = 342.
In my FIXED measure, I force DAX to iterate over each coutry row and check for negative; if no negative it adds the difference to the sum. So, it adds 0+117+63+75+86+16 for a total of 358.
Hi @nikot.23 , thank you very much for your help. It works for the column total, but for row total, the summation still includes negative as shown here:
Would you know how to fix this?
dna enterprise.pbix (285.9 KB)
Since you need to sum over two dimensions, you must supply the appropriate table.
Measure 3 FIXED =
VAR _T = SUMMARIZECOLUMNS( 'DIMS_Country 2023'[Country], DIMS_Dates[Month])
VAR _result =SUMX(
_T,
VAR _diff = [Measure 2]-[Measure 1] RETURN IF(_diff>=0,_diff)
)
RETURN _result
If you need more dimensions they should be added in the SUMMARIZECOLUMNS()
Thank you very much, this works, appreciate it!