Set up a slicer to filter numbers in different columns in Matrix

[ymobbs]

Hi,

I built up a Matrix in power bi. Like this one: 

Customer	Area 1	Area 2	Area 3
Customer 1	90000		210833
Customer 2	78000	9422	99272
Customer 3	10998	23455	31000
Customer 4	4567		178078
Customer 5			117446
Customer 6		157835
Customer 7		141947
Customer 8			136120
Customer 9	12043	1014
Customer 10	374		4490

I want to set up a slicer for 3 areas to be able to sort data in different columns. For example, I choose Area 1, then the whole table will be sorted by descending order of numbers in Area 1 column. 

Is that doable in power bi? 

[Greg_Deckler]

Yes, but you will likely need to create a measure that does a RANKX or returns the value from Area1, Area2 and Area3 based upon the measure selection. You would add that to your matrix visualization and hide the column by turning off word wrap and shrinking it. Then, you could sort the matrix visual by that measure. 

[jdbuchanan71 · Accepted solution]

Hello @ymobbs 

You can do what you are looking for, you will just need an extra table and some additional measures.  I assume your matrix is set up with customer on the rows and area on the columns with an amount in the values.  We will have to change that a bit to fit the sort measure in but it's not too bad.

First we need a table of the areas.

Area Table = 
DATATABLE(
    "Area",STRING,
    {
        {"Area 1"},
        {"Area 2"},
        {"Area 3"}
    }
)

Then we have a measure to sum the amount and a measure for each of the areas.

Total Amount = SUM ( YourTable[Amount] )

Area 1 Amount = CALCULATE ( [Total Amount], KEEPFILTERS ( YourTable[Area] = "Area 1" ) )

Area 2 Amount = CALCULATE ( [Total Amount], KEEPFILTERS ( YourTable[Area] = "Area 2" ) )

Area 3 Amount = CALCULATE ( [Total Amount], KEEPFILTERS ( YourTable[Area] = "Area 3" ) )

Then the sorting measure that will read the slicer we put over the top of the Area table.

Sort Measure = 
VAR SelectedArea = SELECTEDVALUE ( 'Area Table'[Area], "Area 1" )
RETURN
SWITCH (
    TRUE (),
    SelectedArea = "Area 1", [Area 1 Amount],
    SelectedArea = "Area 2", [Area 2 Amount],
    SelectedArea = "Area 3", [Area 3 Amount]
)

You make the slicer on area and add the 4 measures into your matrix then set the sort on the matrix to be by [Sort Measure]

Once you get it working the way you want you can set the [Sort measure] column to be so narrow you can't see it. 

I would set the slicer to be single select only but in my sorting measure I did set the default to use Area 1.  If the user selects 2 areas to sort by the measure will not know which to use so it will default back to Area 1.  Just something to be aware of.

I have attached my sample .pbix file for you to take a look at.