Is it possible to create numeric range slicer and filter data in matrix in Power BI Desktop?

1
votes

I have matrix with two measures: Total Premium and Binds Total .............................................................

enter image description here

Is it possible to create range slicer (something like the one below), that would filter data in a matrix based on amount of premium?

enter image description here

The data in a query looks like this:

enter image description here

So the SUM of Premum per each ControlNo should correspond to Range Slicer.

For example ControlNo 10 should be in range between 10,000 and 20,000. Because 3,000 + 9,000 = $12,000

And ControlNo 20 should be in a range between 20,000 and 30,000 Because 15,000 + 7,000 = $22,000

.PBIX file can be accessed here: https://www.dropbox.com/s/a3l6e51r39t3kd1/GrossBudgetTest.pbix?dl=0

2
powerbidaxpowerbi-embeddedpowerbi-desktop

2 Answers

4
votes

Marco's answer looks good, but you can also achieve the grouping with a calculated column instead of defining a new table and use that column as a slicer:

Range = 
VAR Premium =
    CALCULATE(SUM(fact_Premium[Premium]),
        ALLEXCEPT(fact_Premium, fact_Premium[ControlNo])
    )
RETURN
    SWITCH(TRUE(),
        Premium <= 10000, "From 0 to 10,000",
        Premium <= 20000, "From 10,000 to 20,000",
        Premium <= 30000, "From 20,000 to 30,000",
        Premium <= 40000, "From 30,000 to 40,000",
        Premium >  40000, "More than 40,000"
    )

The downside of this is that it's not a measure and may not interact with your slicers the way you want since calculated columns are unaffected by slicers.

Note Power BI does have some built-in binning capability.

For example, you could right-click on the Premium column and select Group and choose how you want to bin your data.

Binning

That's not going to work for you in this case since you want to bin at an aggregated level.

3
votes

First create a table 'Ranges'. You can import it or use Enter Data on the Home-tab. It should look like this:

enter image description here

Then create these three maesures:

MinThreshold = MIN('Ranges'[Min])

MaxThreshold = MAX('Ranges'[Max])

Total Premium per ControlNo in Range =
VAR selectedControlNo =
    SELECTEDVALUE ( 'fact_Premium'[ControlNo] )
RETURN
    IF (
        CALCULATE ( [Total Premium], 'fact_Premium'[ControlNo] = selectedControlNo )
            >= [MinThreshold]
            && CALCULATE ( [Total Premium], 'fact_Premium'[ControlNo] = selectedControlNo )
                < [MaxThreshold],
        [Total Premium],
        BLANK ()
    )

You cannot use the SELECTEDVALUE function directly in [Filter1] of the CALCULATE function. That's why you have to use the VAR RETURN or the CALCULATE(expression,FILTER()) construct in this maesure.

Now you can create the following matrix & slicer. Put 'Ranges'[Ranges] in the slicer. Put 'fact_Premium'[ControlNo] on Rows and [Total Premium per ControlNo in Range] on Values in the Matrix.

enter image description here