Search code examples
powerbidaxpowerbi-embeddedpowerbi-desktop

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

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

Solution

  • 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.