DAX subtotal at item level for only visible row items

Question

I am having a hard time coming up with the right DAX for something that seems so simple!

Table1                  Table2
Year | Amount           Year
-------------           -----
2010 | 1                2010
2011 | 2                2011
2012 | 3                2012

Relationship:

Table1[Year] related to Table2[Year]

All I'm trying to do is, in a Pivot Table, return the subtotal of all years based on the years selected. So, for example, assume I have a Slicer for Table2[Year] and only 2010 and 2011 are selected (2012 is filtered out). The measure for each row should be 3 (the total of all visible years, or 1 + 2 for years 2010 + 2011), like so:

Rows    Measure
----    -------
2010          3
2011          3
 Total        3

But based on different variations of DAX I've tried, I either get the amount based on each row or I get a subtotal of ALL years, including 2012 which is filtered out. I've tried variations of ALL, CONTAINS, VALUES but I am struggling!

Orlando Mezquita Orlando Mezquita · Accepted Answer · 2016-05-22T11:57:03

Try this:

SUMVisible := CALCULATE(
                        SUM(Table1[Amount]),
                        ALLSELECTED(Table2[Year])
                       )

DAX subtotal at item level for only visible row items

1 Answers