I would like to calculate a subtotal using dax (not using the subtotal feature of matrix visual) Sample data: ClientShopSalesA1100A2200B3400B4500 Desired outcome: I want a measure that can calculate Client total for the displayed Shop without adding the client field itself to the visual ShopClient Total1300230039004900 Best I could come up with was CALCULATE(SUM('Table1'[Sales]); ALL('Table1'[Shop])) but it just gives me a repeating 1200 across all shops as it should unless I add client field to the table. How can I add filter context for the client into the measure if its not part of a visual?

Helper I

7 years ago

Solved

Calculate subtotal using dax

I would like to calculate a subtotal using dax (not using the subtotal feature of matrix visual)

Sample data:

Desired outcome:

I want a measure that can calculate Client total for the displayed Shop without adding the client field itself to the visual

Best I could come up with was

CALCULATE(SUM('Table1'[Sales]); ALL('Table1'[Shop]))

but it just gives me a repeating 1200 across all shops as it should unless I add client field to the table.

How can I add filter context for the client into the measure if its not part of a visual?

Credo Please try this as a "New Measure"

Test107 = CALCULATE(SUM(Test107Measure[Sales]),FILTER(ALL(Test107Measure),Test107Measure[Client]=SELECTEDVALUE(Test107Measure[Client])))