Power BI Measure Row Total with Visual calculations

When calculating a Measure row-by-row total in a Power BI Visual table, we often use the following pattern:

 Calculation1= SUMX (VALUES (Table [AttributeColumnName]), [Measure])        

This formula returns the Measure value for each row in the table and provides a row-by-row total for the entire table.

Recently, Microsoft introduced Visual Calculations, which offer a powerful way to create calculations for a single visual without affecting the report data model.

However, using the direct analog of the above formula in a visual calculation produces an incorrect result for the table total (please see in the example below):

Calculation2 = SUMX( VALUES([AttributeColumnName]), [ValueColumn])        

Fortunately, there is another formula that works correctly in visual calculations and meets the requirement:

Calculation3 = EXPAND (SUM([ValueColumn]),ROWS)         

Want to say huge thank you to Greg Deckler for the idea and championing the topic of measure totals.

Example

Consider we need to calculate absolute difference between revenue and budget for each store and total across our store chain.

Here are the input data we have:

Lest build formulas that will calculate Absolute difference per store and proper total.

First, we create a Measure in the data model:

AbsDiff_MEASURE_SUMX = SUMX(VALUES(SalesByStore[Store]), ABS([RevenueVsBudget]))          

Here for convenience, we referenced a data model measure RevenueVsBudget = SUM(SalesByStore[Revenue])-SUM(SalesByStore[Budget])

Now, let's create Visual Calculations:

ABS_Diff_VC_SUMX = SUMX(Values([Store]), [ABS_Dif_VC])           

where ABS_Dif_VC = ABS([Revenue]-[Budget]) is an intermediary visual calculation (the column is hidden in the visual table).

Here is another visual calculation using Expand:

ABS_Diff_VC_EXPAND = EXPAND(Sum([ABS_Dif_VC]),ROWS)          

Lest look at the results each calculation returns:

You can see that only the SUMX measure and the EXPAND visual calculation produce the Total we were looking for, while SUMX in Visual Calculation didn't work as we expected.

#PowerBI #VisualCalculations