DAX calculate values for each subgroup without applied values filters

Question

I have a pivot table with 2 slicers to manage population (Morning/Afternoon; Visit type), 3 subgroups with ID and Names as rows and 3 calculated measures as columns:
1. Basic count formula:
=COUNTROWS(Visit)
2. Long Median formula:

=(MINX (FILTER (VALUES (Visit[Colum1]),  
  CALCULATE (COUNT (Visit[Colum1]), Visit[Colum1]   
<= EARLIER (Visit[Colum1])) > COUNT ( Visit[Colum1]) / 2), Visit[Colum1]...

3. Lookup/SUM formula:
=IF(CALCULATE(SUM(PE_new[DenominatorNBR2]), RELATEDTABLE(Visit)) < 30, BLANK(), CALCULATE(SUM(PE_new[NumeratorNBR2]), RELATEDTABLE(Visit)) / CALCULATE(SUM(PE_new[DenominatorNBR2]), RELATEDTABLE(Visit)))

The problem I'm trying to find a solution to is to show only rows with values that are equal or greater than 10 in the column C (Measure 1 (Sum)). I can do that using "values filters" for ID or Names columns but in this case subtotals will be changing as well (decreasing the number of records) but I need to show subtotals for each group as a whole, not only calculations for those rows with more than 10 values.

Is there a way to write/change a DAX formula for subtotals where it will calculate values for the whole group, not taking into account "values filters"? Or maybe there are other options to show only rows with values that are equal or greater than 10 but calculate values for the whole group in subtotals?

Will be grateful for any help!

This is original table:

This is what I get now (subtotals are based on the current/filtered pivot records: values that are equal or greater than 10 in the column C (Measure 1 (Sum))):

Looking for this result (subtotals are shown for each group as a whole):

A screenshot with a "values filters" filter:

Alexis Olson Alexis Olson · Accepted Answer · 2018-10-09T21:53:43

You can set your measure to calculate differently depending on whether it is a subtotal or not using functions like HASONEVALUE of ISFILTERED. So if you've used "values filters" to limit which rows you see, you can still get subtotals including all rows by using the appropriate filter context.

E.g., for a basic count, you could write something like this

CountMeasure = 
IF(HASONEVALUE(Visit[ID]),
    COUNT(Visit[Column1]),
    CALCULATE(COUNT(Visit[Column1]), ALLEXCEPT(Visit, Visit[Group]))
)

The ALLEXCEPT function removes all of the filter context except for the column(s) you specify. The same pattern should work for other measures as well:

SubtotalMeasure = 
IF(HASONEVALUE(Visit[ID]),
    [Measure],
    CALCULATE([Measure], ALLEXCEPT(Visit, Visit[Group]))
)

Edit: In response to your comments, it's a bit tricky to get separate slicer selections from value filtering since the DAX measure treats them similarly. However, if they are slicing/filtering on different columns, then it should be possible.

For example, if you had a slicer on ID, then you could pass that column into your ALLEXCEPT function as another argument: ALLEXCEPT(Visit, Visit[Group], Visit[ID]). This would prevent it from removing the slicer filtering on ID.

The standard way to remove filter context while preserving slicer selections is using ALLSELECTED. However, this would preserve the values filtering as well.

You might be able to get the particular filter context you want using a combination of these sorts of this. For example,

SubtotalMeasure =
VAR CurrentGroup = MAX(Visit[Group])
RETURN
IF(HASONEVALUE(Visit[ID]),
    [Measure],
    CALCULATE([Measure],
        ALLSELECTED(Visit),
        ALL(Visit[Name]),
        Visit[Group] = CurrentGroup
    )
)

The would combine all those arguments in an AND fashion. The ALLSELECTED(Visit) would keep all the slicer and value filtering (but not the pivot table context), the ALL(Visit[Name]) would remove the value filtering on the Name column (but also any slicer selections on that column), and Visit[Group] would set the filter context for the Group column to be only the Group value for the current row in the current table that is defined by the variable above.

I can't guarantee that this particular combination will work for your particular setup since I don't know what all columns and slicers and so forth you have, but it should give you an idea of what sort of things you can try.

DAX calculate values for each subgroup without applied values filters

1 Answers