cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
evko
Helper III
Helper III

Ranking of matrix

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: 

 

evko_0-1634735295107.png

 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @evko ,

 

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)

Capture.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

4 REPLIES 4
v-jayw-msft
Community Support
Community Support

Hi @evko ,

 

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)

Capture.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

Marik
Solution Supplier
Solution Supplier

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

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

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

Marik
Solution Supplier
Solution Supplier

It's your rank calculation.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors