Change Total Row Calculation

Share The Knowledge

If totals in your viz aren’t showing what you want DAX can change them.

Requirement

Simple measures calculate as you’d expect (eg ‘Total Sales’). Complex measures may calculate differently depending on the value in the row.

Why does Total Sales RT MTD show 1011 in 2019, 357 in 2020, 357 for Total?

Why the odd totals?

DAX can identify current row value (Month, Year, Total) and modify the calculation.

File

Set-up

Tables & Relationship

Tables Sales and Date have a relationship based on field Date.

Two tables

Fields/Measures in Visual

I use a matrix viz with 3 fields in Rows, original measure ‘Total Sales’ and several running total measures.

Matrix visual

Simple Measure

With ‘Year’, ‘Month Name’, and ‘Date’ in the rows, measure ‘Total Sales’ calculates as expected. ‘Month Name’ = total of ‘Date’ rows. ‘Year’ = total of ‘Month Name’ rows.

Measure ‘Total Sales’

Total Sales = sum(Sales[Sale Amount])

Complex Measure

Complex measures may have misleading totals. I’ll use the RT (running total, aka cumulative total) example. Year & Total rows are confusing. 1011 in 2019, 357 in 2020, 357 for Total? Later I’ll explain why but now I’ll modify the totals.

Total Sales RT MTD = 
CALCULATE(
          [Total Sales],
          FILTER(
                 ALL('Date'),
                 'Date'[Year] = MAX('Date'[Year]) &&
                 'Date'[Date] <= MAX('Date'[Date]) &&
                 'Date'[Month Nmbr] = MAX('Date'[Month Nmbr])
                 
          )   
)

Solution Examples

Changing the calculation for Month, Year, and Total that I’ll explore.

Blank Total

Measure: Total Sales RT MTD no totals

Simply hide values for Month, Year, and Total. Confusion is gone!

Blank totals for Year, Month, Total.

HASONEVALUE identifies if row value is Date. If yes do running total, if no (it’s Month, Year, or Total) then blank.

Total Sales RT MTD no totals = 
VAR final = 
CALCULATE(
          [Total Sales],
          FILTER(
                 ALL('Date'),
                 'Date'[Year] = MAX('Date'[Year]) &&
                 'Date'[Date] <= MAX('Date'[Date]) &&
                 'Date'[Month Nmbr] = MAX('Date'[Month Nmbr])
                 
          )   
)
RETURN
IF(HASONEVALUE('Date'[Date]),final,BLANK())

Change Calculation1

Measure: Total Sales RT MTD total (month is count)

HASONEVALUE tests current row. If a Date do running total, if a Month count rows in table Sales else blank (for Year & Total). Next examples are more practical.

Changing calculation based on current row value
Month = transaction count
Total Sales RT MTD total (month is count) = 
VAR final = 
CALCULATE(
          [Total Sales],
          FILTER(
                 ALL('Date'),
                 'Date'[Year] = MAX('Date'[Year]) &&
                 'Date'[Date] <= MAX('Date'[Date]) &&
                 'Date'[Month Nmbr] = MAX('Date'[Month Nmbr])
                 
          )   
)
RETURN
IF(HASONEVALUE('Date'[Date]),final,
IF(HASONEVALUE('Date'[Month Name]),COUNTROWS(Sales),BLANK()))

Change Calculation2

Measure: Total Sales RT MTD total (month is total)

Date level is a running total. Month level now adds correctly. Total is blank.

change calculation based on current row value. Month total works, overall total is blank
Date=RT, Month total works. Total = blank
Total Sales RT MTD total (month is total) = 
VAR AddMonthSales = SUMX(VALUES('Date'[Month Name]),[Total Sales])
VAR final = 
CALCULATE(
          [Total Sales],
          FILTER(
                 ALL('Date'),
                 'Date'[Year] = MAX('Date'[Year]) &&
                 'Date'[Date] <= MAX('Date'[Date]) &&
                 'Date'[Month Nmbr] = MAX('Date'[Month Nmbr])
                 
          )   
)
RETURN
IF(HASONEVALUE('Date'[Date]),final,
IF(HASONEVALUE('Date'[Year]),AddMonthSales,BLANK()))

Change Calculation3

Measure: Total Sales RT MTD total (all total)

All totals now the same as totals for simple sum measure ‘Total Sales’. Daily level still shows running total.

Total Sales RT MTD total (all total) = 
VAR AddMonthSales = SUMX(VALUES('Date'[Year]),[Total Sales])
VAR final = 
CALCULATE(
          [Total Sales],
          FILTER(
                 ALL('Date'),
                 'Date'[Year] = MAX('Date'[Year]) &&
                 'Date'[Date] <= MAX('Date'[Date]) &&
                 'Date'[Month Nmbr] = MAX('Date'[Month Nmbr])
                 
          )   
)
RETURN
IF(HASONEVALUE('Date'[Date]),final,
IF(NOT HASONEVALUE('Date'[Date]),AddMonthSales,BLANK()))

Why Odd Totals?

Measure: Total Sales RT MTD

Near the top of the post I said I’d explain why this measure gives the odd totals (eg 1011 for 2019 and 357 for Total). I lied. I’ll leave it as homework to study measure ‘Total Sales RT MTD’ and determine why it adds up the way it does. It took me awhile but I finally figured it out!

Summary

Another DAX mystery solved! The further reading links were helpful but for me to really learn I have to dive into the data and experiment to fully understand a concept. Now I love HASONEVALUE!

Further Reading

Great resources I’ve learned from on this topic:


Share The Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *