Power BI - Using a slicer on a Matrix Grand total

Question

I have been working on this Power BI Report and would like some assistance with a slicer used for a matrix.

I need the slicers "MB Data Used", "Calls Made", and "SMS Sent" to slice the Matrix Grand Total fields (on the far right) instead of the value fields. My current slicers work great on the value fields.

Data is filled by a table:

----------------------------------------------------
|ph_id | month | data_used | calls_made | sms_sent |
| 1    | 1/1/19| 123       | 0          | 33       |
| 2    | 1/1/19| 87        | 22         | 0        |
| 3    | 1/1/19| 0         | 0          | 0        |
| 1    | 1/2/19| 0         | 55         | 33       |
| 2    | 1/2/19| 87        | 22         | 77       |
| 3    | 1/2/19| 0         | 0          | 0        |
----------------------------------------------------

Which links to a few others to get related data.

My goal is to be able to see which phone numbers have had no data/call/sms use over the last X months instead of just filtering the ones which contain a 0. In this scenario, when the slicers are all set to 0 and the date range is set 1/1/19-1/2/19, only ph_id 3 should show.

Edit: W.B. - see this image

It seems that you need new column that summarize all columns, and then you can build another slicer on it. — Lev Gelman
Thanks @Lev, I was playing with that but couldn't get the DAX right. Do you have any ideas? — Nathan Wilson
something like CALCULATE(ALL(Dates[Date]), [month] + [calls_made] + [sms_sent] . You need to specify table name before column name and instead Dates[Date] write your date column. Actually you need to build your total, instead using the build in grand total. — Lev Gelman

W.B. W.B. · Accepted Answer · 2021-02-09T05:55:13

You need to use another, unrelated table for your slicers. The best way to create such table is to use the what-if parameter option in the modelling tab (assuming you have any recent version of PBI Desktop).

Or, if you want to base the slicer on number of calls from actual data, you would create the slicer table using New table option and the following formula: CallSlicer = GENERATESERIES(MIN(Data[calls_made]), MAX(Data[calls_made]), 1). The one at the end indicates the step, so you can adjust it, if you want your users to use the slicer in, for instance, increments of 10 or 20.

Now, when you use the generated CallSlicer column, which looks like this:

You will be able to filter your results like this: Your filtered measure = CALCULATE([your_measure], FILTER(Data, Data[calls_made] >= MIN(CallSlicer[CallSlicer]) && Data[calls_made] <= MAX(CallSlicer[CallSlicer]))). You then use your filtered measure in the matrix visual.

EDIT: Here's a working sample: https://1drv.ms/u/s!AmqvMyRqhrBpgtRGGbJ6w-b66uBENQ?e=67JduS

I've updated the sample - now it shows 2 scenarios. One table reacts to the slicer at individual cell level, the other one at the grand total level.

The key to have the first table working is shown above, below is a solution for the second table, that filters rows at the grand total level:

Create a measure that will show sum for all dates/months, as an example:

CallSumTotal =
VAR tab =
    FILTER (
        CALCULATETABLE (
            SUMMARIZE ( Data, Data[id], "calls_made", SUM ( Data[calls_made] ) ),
            ALLSELECTED ( Data[month] )
        ),
        [calls_made] >= MIN ( CallSlicer[Value] )
            && [calls_made] <= MAX ( CallSlicer[Value] )
    )
RETURN
    SUMX ( tab, [calls_made] )

Now in the matrix use a regular sum measure, but create a visual level filter for CallSumTotal and set it to is not blank

Power BI - Using a slicer on a Matrix Grand total

1 Answers