DAX calculated measure in Power Pivot 2016

Question

I have a data model which I use in Excel 2016 Power Pivot. Here is a simplified version of the fact table and dimension table:

I am trying to calculate a calculated measure that sums the Amt from the Group field. For example, if I am filtered on subgroup 1, I would like the measure to show me '21' which is the sum of Group 'A'.

I have tried the following:

Measure = 
CALCULATE(
          SUM(FactTable[Amt]),
          FILTER(DimesionTable,DimensionTable[Subgroup]=MAX(DimensionTable[Subgroup]))
)

But I get an error saying Max can only be applied to numeric values. How can I create this measure in Power Pivot 2016?

cant u just use the column [group] in the slicer instead of subgroup — BOUBRIT Nacim
I would like to filter down to the subgroup, but also have a view of the group at the same time. There are multiple measures in the fact table. I can hardcode the filter, but there are many groups. — Wadstk
It sounds like your Subgroup column might be a string data type — DataGuy

BOUBRIT Nacim BOUBRIT Nacim · Accepted Answer · 2020-11-18T20:24:30

Use this code

 Measure = 
CALCULATE(
          SUM(FactTable[Amt]),
          ALLEXCEPT(DimensionTable,DimensionTable[Group])
)

DAX calculated measure in Power Pivot 2016

1 Answers