Subtotal not reflecting sum of underlying row calculations

Question

This is kind of similar to my question here, but different enough i think to justify a new question. Looking at the below table, i want to take the total of Direct Expense across all regions, and subtract that from the Americas Expense number only, then total up the result.

I'd like the last column's total to read 17,661, not 54,888. Here is a link to a sample workbook on OneDrive with the above table: https://1drv.ms/u/s!Al7VQqB8RVlWgY4mUYqouesRPNE0qw?e=IiMPnq Any ideas?

sergiom sergiom · Accepted Answer · 2020-12-31T17:38:47

Your measure had two issues:

it was missing the context transition, this can be fixed adding CALCULATE where a context transition is needed
The [Total Direct Expense total for Region (c)] uses ALLSELECTED, ad it's called inside an iterator. But we can move the measure before the iteration using a variable instead. (The number 13,703 is wrong)

The measure then becomes

Final Result (a-c) = 
VAR TotalExpense = [Total Direct Expense total for Region  (c)]
RETURN
SUMX (
    VALUES ( Sheet1[Region] ),
    IF (
        Sheet1[Region] = "Americas",
        CALCULATE (
            SUM ( Sheet1[Total Expense (a)] ) - TotalExpense
        ),
        CALCULATE (
            SUM ( Sheet1[Total Expense (a)] )
        )
    )
)

Now the final result is

A rather complex article about ALLSELECTED explaining what is the shadow filter and why calling ALLSELECTED after a context transition in an iteration doesn't work as expcected can be found here https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

Subtotal not reflecting sum of underlying row calculations

1 Answers