All - I am running into a cumulative total that is resetting incorrectly. I have two types (Baseline and Fast) of MW. I have summed Baseline and Fast in each measure.
In Adjusted Total Fast MW, I want to return [Total Baseline MW] if [Total Fast MW] is blank or if it is greater than [Total Baseline MW]. Otherwise, I want to return [Total Fast MW]. This appears to be working correctly within the context of each year, but the total should be 180 as opposed to 100.
When I apply a cumulative total approach to the Adjusted Total Fast MW, the total appears to reset when a non-blank [Total Fast MW] is returned (year 2023 in the table for instance).
I’ve tried Sam’s Fixing Incorrect Totals approach ( Fixing Incorrect Totals Using DAX Measures In Power BI | Enterprise DNA, but that didn’t seem to do the trick.
Can someone help me please?
Baseline vs Fast.pbix (150.2 KB)
Hello, @matthewcousins07
I’ve added one new and updated cumulative measures to your workbook.
1st one - Adjusted Total Fast MW (1)
Adjusted Total Fast MW (1) =
IF ( HASONEVALUE(‘Dates’[Year]),
– check, if row level has Year value
– and if “Yes”, return further calculation:
IF ( OR ( [Total Fast MW] = BLANK(), [Total Fast MW] > [Total Baseline Mw]), [Total Baseline Mw],[Total Fast MW]),
– if not, for Total row level
SUMX (
– use SUMX to have an opportunity to provide calculations inside virtual table
SUMMARIZE(
– create virtual table
‘Data’,
– from Data table
‘Dates’[Year],
– first column is Year values
“Adjusted”,
– second column values from IF and OR logic (previous step)
IF ( OR ( [Total Fast MW] = BLANK(), [Total Fast MW] > [Total Baseline Mw]), [Total Baseline Mw],[Total Fast MW])
),
– returns sum of values in column “Adjusted”
[Adjusted]
)
)
Baseline vs Fast.pbix (154.0 KB)
- Cumulative Adjusted Total Fast MW - just change base measure for new one
Hope it helps you!
BR,
Oleg
1 Like
That worked well! Thanks Oleg!
