Ranking of matrix

[Anonymous]

Hi All, 

My data is in a matrix (like below) and I would like the ranking on TYPE to be fixed 1) shoes 2) clothes 3) accessories 4) kitchen but when i expand the + sign, i would like the measure to rank by descending order. is this even possible? thanks alot for the valuable help 🙂 

My dataset looks like this: 

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

You will need to create another measure to calculate the value by type.

For example:

Measure = SUM('Table'[value])

Measure 2 = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[type]))

Then use ISFILTERED() function to get different rank.

Measure 3 = 
var _unexpand = RANKX(ALLSELECTED('Table'),[Measure 2],,ASC,Dense)
var _expand = RANKX(ALLEXCEPT('Table','Table'[color]),[Measure],,DESC)
return
IF(ISFILTERED('Table'[color]),_expand,_unexpand)

Best Regards,

Jay

[Anonymous]

@Anonymous Function ISINSCOPE can help you. Measure should look like this:

measure=IF(ISINSCOPE([Colour]),_descRank,_ascRank)

[Anonymous]

Hi, sorry what is _descrank? is that also a measure? 

[Anonymous]

It's your rank calculation.