Possible to keep subtotals static in matrix?

[iggypop]

Hello,

This is a simple solution in Tableau but it seems like a nightmare in Power BI. I have a dataset where I would like to put into a matrix and keep the subtotals static. The only filter I would like the subtotals to be dependent on is the date column. Is there a way to accomplish this? I have provided some screenshots below of how I have managed this in Tableau for some context.

Tableau Formula:

state = lookup(attr([state]),0)

Below is a screenshot of the tableau report with the test data

Below is a screenshot of the same table filtering out "MI". However the Grand Total and Subtotals have remained persistent. 

I have tried the sumx solution below but this is only referencing 1 measure while the solution in Tableau reference all measure in the table. Moreover, it copies the value of the subtotal for each row instead of just the subtotal row.

SUMX(ALL(Query[Affiliate_Name_Txt]), Query[qps])

Hopefully there is a simple solution and any assistance would be greatly appreciated. Thank you.

[OwenAuger · Accepted solution]

Hello @iggypop 

This is certainly one of those areas where Power BI doesn't offer the same features as Tableau.

Power BI has no general setting for ignoring particular filters in subtotals/grand totals of a matrix visual.

In order to get the same effect and have it apply to any measure, I would suggest using a Calculation Group.

(see here for intro to Calculation Groups if needed).

This Calculation Group would have to contain a Calculation Item with an expression that calculates the selected measure ignoring particular filters (in this case the State filter) when at a total level, detected using ISINSCOPE. In this example, we would have to assume that a "total" is any level of the matrix hierarchy where state is not the current level of the hierarchy.

For example, the expression for the Calculation Item could be:

IF (
    ISINSCOPE( Data[state] ),
    SELECTEDMEASURE (),
    CALCULATE (
        SELECTEDMEASURE (),
        REMOVEFILTERS ( Data[state] )
    )
)

REMOVEFILTERS(...) could be replaced by another modifier or modifiers, such as ALLEXCEPT, if you needed different behaviour.

Note that any measures used in the visual would have to be explicit measures in order for the Calculation Item to be applied.

I have attached a small example to illustrate the concept.

The Calculation Item "Ignore State Filter in Totals" is applied to the first matrix but not the second.

Hopefully that's some help.

Regards,

Owen

[iggypop]

@OwenAuger Many thanks! I've tested on my dataset and it worked. I substituted the REMOVEFILTERS function with the ALLEXCEPT function since I wanted to specifically declare which filters would affect the matrix totals. Below is the code I used for one explicit measure.

qps_test = if ( 
    ISINSCOPE(Query[Affiliate_Name_Txt]), 
    Query[exp_qps], 
    CALCULATE( 
         Query[exp_qps], 
        ALLEXCEPT(Query,Query[qp_date], Query[days_after], Query[state], Query[reporting channel clean]) 
    ) 
)

I did have one question which is more of a convenience question. I noticed I had to use the code above for each separate explicit measure in my visualization but was wondering if there is an easier way. I downloaded your sample pbi report and I saw that you imported a separate table for the Subtotal Calc. Seemed like an easier approach but couldn't figure out how you managed to do that. 

[OwenAuger]

Glad to hear it! 🙂

Regarding Subtotal Calc in my sample PBIX, that is a Calculation Group, which is a special table that is created with Tabular Editor. Calculation Groups contain Calculation Items, and allow you to apply the same modifications to any measures in a visual. This seemed a good approach in this case as you want to apply the same logic to any measure you might include in the matrix.

The code I posted earlier was the code of the Calculation Item (also named Subtotal Calc) which referred to SELECTEDMEASURE(). Within a Calculation Item, SELECTEDMEASURE() refers to the "current" measure, whatever it might be.

With your code above, you can create a Calculation Item with the below expression, using SELECEDMEASURE () instead of a specific measure reference:

IF (
    ISINSCOPE ( Query[Affiliate_Name_Txt] ),
    SELECTEDMEASURE (),
    CALCULATE (
        SELECTEDMEASURE (),
        ALLEXCEPT (
            Query,
            Query[qp_date],
            Query[days_after],
            Query[state],
            Query[reporting channel clean]
        )
    )
)

In order to apply a single Calculation Item to a visual, you can add a visual level filter using the Calculation Group column, filtered to the particular Calculation Item. It will then apply to any measure in the visual. Note that Calculation Items only apply to explicit measures written with DAX. Once you have at least one Calculation Group in the dataset, the Power BI interface will not allow you to create implicit measures by adding columns into visuals.

To create Calculation Groups, you need Tabular Editor, and here is a good video on how to do this:

https://www.sqlbi.com/tv/creating-calculation-groups-in-power-bi-desktop-using-tabular-editor/

Regards,

Owen

[iggypop]

Amazing. I'll try the Calculation Group method when I get a little more familiar with PBI. Thank you again Owen. Appreciate the help here.