Unique order in the rows in the matrix visuals?

[Anonymous]

Hi,

I tried to create a matrix visuals, with an unique order in the rows, not abc, not high to low, there is no logic behind it. Is anybody has info about it how? What I try:

I export my data from excel. I have 1 data table, and I create 1 table with the ‘ranks’ /order I want to use, the second table really easy, just the account number+ rank (order) that I would like to use. I connect this 2 tables.

When I create a table visual everything working fine. I see the first column the rank second is the account so when I sort to A-Z I see the account the order that I want. But I also want to use the drill down functionality (because my accounts have 3 sub categories)+ the possibility to the end users to see records and export data. As far as I see in the table visuals 2 not available ( drill down+export data)..

Any idea what should I use to create a unique row order with the possibility to drill down+ allow the end user to export the raw data?

[AnthonyTilley]

If i have your explination correct then what you want to do is add a custom sort to a set of headers within a matrix 

For example the names below would normal be ordered as follows but you would like to chose the order

Name

Andrew

Bev

Carl

Dave

Jim

To do this i would create a new table in your data model that has a list of all the headers and then assign each one a orderNo example below

Name	OrderNO
Andrew	2
Bev	3
Carl	1
Dave	5
Jim	4

You can then join this table to your model on the name colunm and then use the OrderNo to sort your headers in your table 

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column#targetText=There's%20another%20way%...

[Anonymous]

Thanks Anthony@

I tried but (maybe) because I try to modify a matrix visuals the sort by column button in the modelling tab is inactive.

I create a new column in Excel and add manually the order

01cat

02cat

so currently I manage the correct order, however my column values started with 01,02 etc....

[AnthonyTilley]

Are you able to provide a sample PBIX file

[Anonymous]

yes, thank you for the help

i uploaded it here

https://gofile.io/?c=hRGCqJ

THank You

[AnthonyTilley · Accepted solution]

okay i think i see where your going wrong 

you do not sort by colunm by clicking into the matrix you want to sort. 

step 1 (2) - Create a relationship between your High cost cat old Colunm in sheet 2 and your High cost cat colunm in sheet 1

Step 2 - go to the data view tab and select the colunm in sheet 1

Step 3 - click on sort by colunm and select your rank colunm

Step 4 - Sort the matrix

if you go to your report page and remove the rank colunm your matrix will now sort based on the rank number 

the colunms you added to the bottom will not show untill you have a measure to populate them 

i usually accomplich this by creating diffent measures for each total and the using a switch function to jump between 

for example lets say your all of the headers up to number 22 OTHR are all Sum or Value

and Total head count is a count of all values

you can create a measure for each as below

Measure1 = sum('Sheet1 (2)'[value])

Measure2 = CALCULATE(count('Sheet1 (2)'[value]),ALL('Sheet1 (2)'))

then use a switch based on the header rank to do this you need a max rank header

maxOrder = max(Sheet1[rank])

to switch based on the rank 

Measure_Comp = 
If([maxOrder] <= 22 , [Measure1],

SWITCH([maxOrder], 

            23, [Measure2],
            24, "COMPENS",
            "no measure yet defined"
            

))

in the example above you would need to set a measure for each header in your table

in my example anything up to rank 22 wil just be the sum of value, 23 wil be a count of all rows in sheet 2 

24 will just say compens and everything else will be defaulted to say no measure yet defined.

as you can see in the matrix all headers are ordered correctly withour the need for a rank colunm in the matrix 

and the measure creates a diffrent result based on the rank number 

let me know if this helps.