Keep a permanent row visible in a matrix even when filtering

[Anonymous]

Hi,

First post in the community after a lot lurking!

As stated in the subject I would like to be able to keep some rows in a matrix even if a slicer is currently filtering it.

So looking below I can see that BRAND H has Sales within CABRIO and SUV, with 50% share in the first and 100% share in the second. The objective is to be able to see also the rows for SEDAN and SPORT even if BRAND H has no sales whitin these two types (and thus 0% share) but still showing the correct brand share in CABRIO and SUV.

In brief, the slicer should be filtering only the brand share column (so dynamically change based on the selected brand).

Thanks a lot for the help!

Best,

M.

[DataInsights · Accepted solution]

@Anonymous,

Try this solution.

1. Create a table with car brands (one row per brand). No relationship exists between this table and the data table. One option is to create a calculated table as below:

CarBrands = DISTINCT ( CarSales[BRAND] )

2. Create a Brand slicer based on the table CarBrands.

3. Create measure:

Brand Share = 
VAR vSelBrand =
    SELECTEDVALUE ( CarBrands[BRAND] )
VAR vBrandSales =
    CALCULATE ( SUM ( CarSales[SALES BY TYPE] ), CarSales[BRAND] = vSelBrand )
VAR vTotalSales =
    SUM ( CarSales[SALES BY TYPE] )
VAR vResult =
    IF ( ISBLANK ( vBrandSales ), 0, DIVIDE ( vBrandSales, vTotalSales ) )
RETURN
    vResult

4. Create matrix. Interactions between the slicer and matrix should be enabled.

[Anonymous]

@DataInsights 

Thanks a lot, this works perfectly!

[DataInsights]

@Anonymous,

Glad to hear that works. Here's a version of the measure that works for both single and multiple Brand slicer selections:

Brand Share = 
VAR vSelBrand =
    ALLSELECTED ( CarBrands[BRAND] )
VAR vBrandSales =
    CALCULATE ( SUM ( CarSales[SALES BY TYPE] ), CarSales[BRAND] IN vSelBrand )
VAR vTotalSales =
    SUM ( CarSales[SALES BY TYPE] )
VAR vResult =
    IF ( ISBLANK ( vBrandSales ), 0, DIVIDE ( vBrandSales, vTotalSales ) )
RETURN
    vResult