Forum Discussion
Helper IIGetting top 10 values and ranking the same values alphabetically
I have a bar chart above and as you can see I have 20 dealers. Dealer 1 to Dealer 3 have values while the remaining ones have 0. When I try to apply a filter to this visual to get the top 10, it still shows all 20 dealers probably due to the fact that Dealers 4 to Dealers 20 all have 0.
Now what I want is that when I try to get the top 10 and there are Dealers that have the same values (In this case 0) I want those with the same values to be arranged in alphabetical order instead.
So the order in the X axis should look like this:
Dealer 3, Dealer 2, Dealer 1, Dealer 4, Dealer 5, Dealer 6, Dealer 7, Dealer 8, Dealer 9, Dealer 10
Is this possible?
Hi RingoSun,
As far as I know, [Dealer] column is text data type. We couldn't sort text data type column like number type column.
So the order in the X axis should look like this:
Dealer 3, Dealer 2, Dealer 1, Dealer 10, Dealer 11, Dealer 12, Dealer 13, Dealer 14, Dealer 15, Dealer 16
I suggest you add a whole number data type [Dealer ID] column in your table. Duplicate [Dealer] column and replace "Dealer " by blank in Power Query Editor. Then change the column type to whole number and rename it as "Dealer ID".
Measure:
Rank = VAR _SUMMARIZE = SUMMARIZE ( ALL ( 'Table' ), 'Table'[Dealer ID], 'Table'[Dealer], "Value", CALCULATE ( SUM ( 'Table'[Value] ) ) ) VAR _ADDRANK = ADDCOLUMNS ( _SUMMARIZE, "RANK BY VALUE", RANKX ( _SUMMARIZE, [Value],, DESC, DENSE ), "RANK BY ID", RANKX ( FILTER ( _SUMMARIZE, [Value] = 0 ), [Dealer ID],, ASC, DENSE ) ) VAR _NEWRANK = ADDCOLUMNS ( _ADDRANK, "New Rank", VAR _MAXRANKVALUE = MAXX ( _ADDRANK, [RANK BY VALUE] ) RETURN IF ( [RANK BY VALUE] <> _maxrankvalue, [RANK BY VALUE], [RANK BY ID] + _MAXRANKVALUE - 1 ) ) RETURN SUMX ( FILTER ( _NEWRANK, [Dealer ID] = MAX ( 'Table'[Dealer ID] ) ), [New Rank] )Add this measure into the visual level filter and set it to show items when value is less than or equal to 10.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi RingoSun,
As far as I know, [Dealer] column is text data type. We couldn't sort text data type column like number type column.
So the order in the X axis should look like this:
Dealer 3, Dealer 2, Dealer 1, Dealer 10, Dealer 11, Dealer 12, Dealer 13, Dealer 14, Dealer 15, Dealer 16
I suggest you add a whole number data type [Dealer ID] column in your table. Duplicate [Dealer] column and replace "Dealer " by blank in Power Query Editor. Then change the column type to whole number and rename it as "Dealer ID".
Measure:
Rank = VAR _SUMMARIZE = SUMMARIZE ( ALL ( 'Table' ), 'Table'[Dealer ID], 'Table'[Dealer], "Value", CALCULATE ( SUM ( 'Table'[Value] ) ) ) VAR _ADDRANK = ADDCOLUMNS ( _SUMMARIZE, "RANK BY VALUE", RANKX ( _SUMMARIZE, [Value],, DESC, DENSE ), "RANK BY ID", RANKX ( FILTER ( _SUMMARIZE, [Value] = 0 ), [Dealer ID],, ASC, DENSE ) ) VAR _NEWRANK = ADDCOLUMNS ( _ADDRANK, "New Rank", VAR _MAXRANKVALUE = MAXX ( _ADDRANK, [RANK BY VALUE] ) RETURN IF ( [RANK BY VALUE] <> _maxrankvalue, [RANK BY VALUE], [RANK BY ID] + _MAXRANKVALUE - 1 ) ) RETURN SUMX ( FILTER ( _NEWRANK, [Dealer ID] = MAX ( 'Table'[Dealer ID] ) ), [New Rank] )Add this measure into the visual level filter and set it to show items when value is less than or equal to 10.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
