I have a table visual, no matter how i try get a total of only the negative numbers summed up it still calculates completely wrong. For example the total should be -79000, and I am getting -3278
My DAX
TotalCalculatedValue2 =
SUMX(
MeterTable,
SWITCH(
TRUE(),
MeterTable[Meter] = “Colour”, [Diff XSM-XERM] * 0.05,
MeterTable[Meter] = “Mono”, [Diff XSM-XERM] * 0.005,
0 – Default value if neither Colour nor Mono
)
)
Hi @Tyronne - Please share a sample PBIX file for further help.
Thanks
Ankit J
Thanks you will find in most cases, there is no way we can send PBIX files especially from firms, as its seen as a major security issue, thanks anyway.
Hi @Tyronne - No need to send your actual file. Just a sample PBIX file with some sample masked data and model.
Can refer to this video for help https://www.youtube.com/watch?v=VmWD7Ayw_NI&t=575s
Thanks
Ankit J
Here is a test file with the example and some text boxes explaining the issue I am facing, many thanks.
TestFIle.pbix (1.1 MB)
Try this:
TotalCalculatedValue2_NegativeOnly =
SUMX(
MeterTable,
VAR CalculatedValue =
SWITCH(
TRUE(),
MeterTable[Meter] = "Colour", [Diff XSM-XERM] * 0.05,
MeterTable[Meter] = "Mono", [Diff XSM-XERM] * 0.005,
0
)
RETURN
IF(CalculatedValue < 0, CalculatedValue, 0)
)
This measure calculates CalculatedValue once per row using SWITCH and then filters out non-negative results with the IF statement. This should yield the correct total by summing only negative values, but if this still doesn’t produce the expected result, please share a minimal .pbix example with some sample data.
Thanks for sharing the sample.
The issue you’re seeing is rooted in how DAX handles filter context at the total level.
Your original measure calculates the maximum value for each serial number over the last three months, but in the total row, DAX removes the row context for each serial number and finds the overall maximum across the entire dataset instead. This is why the total doesn’t match the sum of individual max values.
If you want the total row to reflect the sum of each serial number’s maximum over the last three months, you can replace your Max_Last_3_MonthsXSM measure with:
TotalMax_Last_3_MonthsXSM =
SUMX(
SUMMARIZE(
XSM,
XSM[Serial],
"MaxValue",
CALCULATE(
MAX(XSM[XSValue]),
DATESINPERIOD(
XSM[Date],
MAX(XSM[Date]),
-3,
MONTH
)
)
),
[MaxValue]
)
SUMMARIZE creates a vtable that groups by Serial and calculates each serial number’s maximum value over the last three months, CALCULATE with DATESINPERIOD filters the data to the last three months for each serial number, then finds the maximum XSValue within that period. Finally, SUMX iterates over the virtual table, summing the MaxValue for each serial number. This ensures that the total row represents the sum of individual max values rather than a single maximum for the entire dataset.
You can apply a similar pattern to other measures where you want the total row to aggregate individual row calculations rather than recalculating across the whole dataset.
Thanks did not work, gives me a 0 total as did my attempts at this
Interesting. I got the max of the values shown in the column:
But I removed the visual filter that was filtering the table to only those values where TotalCalculatedValue < 0.
Yes i get to this point and no further. need the sum of the column max.
I get the max of the XSM value for the last 3 months, if there is the same value for two of the months i only want one(which we get), just to sum up those single values within that column.
Sorry. I did read it 
Max_Last_3_Months_Sum =
SUMX(
VALUES(XSM[Serial]),
VAR CurrentDate = MAX(XSM[Date])
VAR LastThreeMonthsPeriod =
DATESINPERIOD(
XSM[Date],
CurrentDate,
-3,
MONTH
)
VAR MaxValueLast3Months =
CALCULATE(
MAX(XSM[XSValue]),
LastThreeMonthsPeriod
)
RETURN
MaxValueLast3Months
)
Thank you HufferD, that worked, youre a star!