Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Clara
Advocate II
Advocate II

Rank by sorting columns

I have a table visual with 4 columns. One lists all units in my company, two of them show percentual values by unit and the other ranks each unit by their values. I want to be able to sort by one of the columns and dynamically change the ranking column so that it always ranks units from first to last depending on the sorted column (I do not need to sort by the rank column itself):

 

RankUnitColumn 1  Column 2
1Unit AA50,0%33,3% 
2Unit BB42,9%30,9%
3Unit CC39,3%55,1%
4Unit DD37,7%37,9%
5Unit EE29,3%18,2%
6Unit FF26,4%24,7%

 

RankUnitColumn 1 Column 2 
3Unit CC39,3%55,1%
4Unit DD37,7%37,9%
1Unit AA50,0%33,3%
2Unit BB42,9%30,9%
6Unit FF26,4%24,7%
5Unit EE29,3%18,2%


Right now my rank measure looks like this:

 

Ranking = RANKX(ALLSELECTED(Table1[Unit]);[Column1])

 

And each of my columns is a measure of its own:
Column1 = 
    VAR Profit = CALCULATE(...)
    VAR Revenue = CALCULATE(...)

    RETURN DIVIDE(Profit;Revenue;BLANK())
Column2 = 
    VAR Profit = CALCULATE(...)
   VAR Revenue = CALCULATE(...)
VAR Extras1 = CALCULATE(...)
VAR Extras2 = CALCULATE(...)

VAR R01 = DIVIDE(Profit+Extras1;Revenue;BLANK())
VAR R02 = DIVIDE(Profit+Extras2;Revenue;BLANK())

RETURN SWITCH(MAX(Toggle[Select]);
"Extras 1";R01;
"Extras 2";R02;
BLANK())
"Toggle" is a table with two rows I am using in a slicer (user selects whether they want to calculate measures with "Extras 1" or "Extras 2"). It is also used in other pages for different purposes:
 
SelectColumnAnswerChoice
Extras 11Yes0
Extras 22No1
I'd rather not use a matrix visual because it doesn't let you sort columns (which is why I'm using two measures instead of one).
 
Is it even possible to achieve what I need?
3 ACCEPTED SOLUTIONS

So I've actually spent WAY  more time than is necessary trying to figure out this issue.  It should be essentially the same use case as adding a row number to a table visual.  However, it doesn't seem like there is a way (even a crazy workaround) to do this unless you're sorting by one column at all times.   It makes sense, but there just doesn't seem like there's a way to determine what the current sort column/order of a visual is in a measure.   

 

You could try to hack it by adding a 2nd table that just has rank # in it and moving it so that it looks like it matches the table of values, but that won't be linked to the first table's current slicer selection or any scrolling.

 

I was thinking about setting up some sort of slicer option that essentially switches between two RANK measures based on the user clicking some slicer option that also switches the sort order, but then  the sort by functionality of the table would break it anyway. 

 

I've gone ahead and voted for this improvement here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19331770-row-number and I suggest you do too

View solution in original post

@Clara If it would be acceptable for the users to select the column to sort by in a slicer instead of the matrix then @Cmcmahan idea of a ranking over a switch would work.  You just have to set up a table of the values to sort by and modify your ranking formula. 

You can even make the table just using a DAX formula, here is an example from one of my models.

=DATATABLE( 
	"Top N Measure", STRING,
	"Order",INTEGER,
{

{"Billed $",1},
{"Allowed $",2},
{"Not Covered $",3},
{"Paid $",4},
{"Claim Count",5},
{"Claimants",6},
{"Claim Lines",7},
{"Avg Billed Line Amount",8}
}
)

Then I read the users selecton in the switch, you would do your ranking over this.

TopN Measure Calc:= 
VAR SelectedMeasure = SELECTEDVALUE ( ctTopNMeasures[Top N Measure], "Paid $")

RETURN
SWITCH ( TRUE (),
    SelectedMeasure = "Billed $", [Billed Amount],
    SelectedMeasure = "Allowed $", [Allowed Amount],
    SelectedMeasure = "Not Covered $", [Not Covered Amount],
    SelectedMeasure = "Paid $", [Paid Amount],
    SelectedMeasure = "Claim Count", [Claim Count],
    SelectedMeasure = "Claimants", [Claimant Count],
    SelectedMeasure = "Claim Lines", [Claim Lines Count],
    SelectedMeasure = "Avg Billed Line Amount", [Avg Billed Line Amount]
)

View solution in original post

Cmcmahan
Resident Rockstar
Resident Rockstar

You might be able to get real clever and hide the headers of the table or cover the header row with a text box so that the user can't click the sorting option.

View solution in original post

9 REPLIES 9
Iamnvt
Continued Contributor
Continued Contributor

hi,

 

for me, it looks like you want to fix the Rank column, no matter the sorting criteria.

Maybe, one simple solution is to insert the Rank column as an image, or ... another table.

 

you can check the PBI file here

https://1drv.ms/u/s!Aps8poidQa5zk6pTG7QrQtQlWo45-Q

So I've actually spent WAY  more time than is necessary trying to figure out this issue.  It should be essentially the same use case as adding a row number to a table visual.  However, it doesn't seem like there is a way (even a crazy workaround) to do this unless you're sorting by one column at all times.   It makes sense, but there just doesn't seem like there's a way to determine what the current sort column/order of a visual is in a measure.   

 

You could try to hack it by adding a 2nd table that just has rank # in it and moving it so that it looks like it matches the table of values, but that won't be linked to the first table's current slicer selection or any scrolling.

 

I was thinking about setting up some sort of slicer option that essentially switches between two RANK measures based on the user clicking some slicer option that also switches the sort order, but then  the sort by functionality of the table would break it anyway. 

 

I've gone ahead and voted for this improvement here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19331770-row-number and I suggest you do too

@Cmcmahan @Iamnvt Thank you both!
At first I thought of resorting to adding a second table just for the ranking and make it look like they are the same table, but that wouldn't work out on PBI mobile (which my boss is adamant about us using), and as the list of units grow it would absolutely need scrolling, so I ruled it out.

I've come to the same conclusion as @Cmcmahan a little late, then looked up if there was any way of showing a (dynamic) row number column on a table visual, but also came up with nothing. I've upvoted the idea, thanks!

 

I guess for now I'm just gonna tell my boss we'll have to make do without a ranking column, or maybe have to split the table into two 😞

@Clara If it would be acceptable for the users to select the column to sort by in a slicer instead of the matrix then @Cmcmahan idea of a ranking over a switch would work.  You just have to set up a table of the values to sort by and modify your ranking formula. 

You can even make the table just using a DAX formula, here is an example from one of my models.

=DATATABLE( 
	"Top N Measure", STRING,
	"Order",INTEGER,
{

{"Billed $",1},
{"Allowed $",2},
{"Not Covered $",3},
{"Paid $",4},
{"Claim Count",5},
{"Claimants",6},
{"Claim Lines",7},
{"Avg Billed Line Amount",8}
}
)

Then I read the users selecton in the switch, you would do your ranking over this.

TopN Measure Calc:= 
VAR SelectedMeasure = SELECTEDVALUE ( ctTopNMeasures[Top N Measure], "Paid $")

RETURN
SWITCH ( TRUE (),
    SelectedMeasure = "Billed $", [Billed Amount],
    SelectedMeasure = "Allowed $", [Allowed Amount],
    SelectedMeasure = "Not Covered $", [Not Covered Amount],
    SelectedMeasure = "Paid $", [Paid Amount],
    SelectedMeasure = "Claim Count", [Claim Count],
    SelectedMeasure = "Claimants", [Claimant Count],
    SelectedMeasure = "Claim Lines", [Claim Lines Count],
    SelectedMeasure = "Avg Billed Line Amount", [Avg Billed Line Amount]
)

@jdbuchanan71 It would work wonders! I'm just worried the layman might try to sort by the headers anyway and decide it doesn't work. I might try it though Smiley Happy Thank you!

Cmcmahan
Resident Rockstar
Resident Rockstar

You might be able to get real clever and hide the headers of the table or cover the header row with a text box so that the user can't click the sorting option.

@Cmcmahan Good idea! I could even hide it with the slicer itself (say, a chiclet slicer) so it still looks like it's sortable Smiley Happy I'd still have to make a separate table (sans ranking) for the mobile version, but the desktop one would appear to work. Thanks again!

Cmcmahan
Resident Rockstar
Resident Rockstar

If your table is sorted by Column1, then the value on top is always going to be Rank = 1, next is Rank=2, etc. right?  

 

It seems that the table you shared is already accomplishing what you want. I guess I'm not sure what the problem you're having is. 

 

What result are you currently getting that isn't correct?

@Cmcmahan I'd like the rank to still go from 1-6 when I sort by Column 2 as well. As it is, it stays ranked by Column 1 and ends up looking all weird 😞 I've edited the original post for clarity.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.