Sooner or later you’ll want to change how a visual total works. Here’s a good example.

Requirement

A measure subtracts an amount from every row in the visual.

Revenue -50 = SUM(fctSales[Revenue])-50

It works for rows but not for grand total (only subtracts 50). I want the total to subtract 50 for each product in the viz.

Desired Output

Measure ‘Revenue CORRECT‘ has desired amounts for each row and total row.

I added a slicer to easily change the subtraction amount (not always -50)

Power BI File

Measures

I had to break down the problem before determining how to solve it.

Revenue Basic

I included measure ‘Revenue Basic‘ to verify final measure ‘Revenue CORRECT’:

Revenue Basic = SUM(fctSales[Revenue])

Revenue -slicer

Modified measure Revenue -slicer (uses slicer value) works for rows but not total. It’s referenced in final measure:

Revenue -slicer = SUM(fctSales[Revenue]) -[Subtract Amount Value]

Revenue CORRECT

Measure ‘Revenue CORRECT‘ correctly calculates at both row & total levels:

Revenue CORRECT = 
VAR _table = SUMMARIZE(fctSales, dimProduct[Product], "rev", [Revenue -slicer])
VAR _final = IF(
                HASONEVALUE('dimProduct'[Product])
                ,[Revenue -slicer]
                ,SUMX(_table,[rev])
             )
RETURN
_final

Functions IF & HASONEVALUE determine when to use either the row measure or the grand total measure.

VAR _table is a virtual table used in the total row. For each [Product] measure [Revenue -slicer] is calculated. The SUMX adds them all.

Recap

Where am I?

The logic may be even more complex but the same concept can be used. Functions IF (or SWITCH) and HASONEVALUE can determine what the active visual cell is:

• non total row (use base measure)
• row total (column context but no row context)
• column total (row context but no column context)
• grand total (what we saw here)

EnterpriseDNA

After I solved this I later found two videos from EnterpriseDNA in section DAX FORMULA DEEP DIVES:

1. Controlling Totals and Subtotals in Power BI
2. How To Fix Matrix Totals in Power BI

Both videos are really helpful. The 2nd video had a complex case! Note: membership is required (it’s worth it!).