Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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):
Rank | Unit | Column 1 ▼ | Column 2 |
1 | Unit AA | 50,0% | 33,3% |
2 | Unit BB | 42,9% | 30,9% |
3 | Unit CC | 39,3% | 55,1% |
4 | Unit DD | 37,7% | 37,9% |
5 | Unit EE | 29,3% | 18,2% |
6 | Unit FF | 26,4% | 24,7% |
Rank | Unit | Column 1 | Column 2 ▼ |
3 | Unit CC | 39,3% | 55,1% |
4 | Unit DD | 37,7% | 37,9% |
1 | Unit AA | 50,0% | 33,3% |
2 | Unit BB | 42,9% | 30,9% |
6 | Unit FF | 26,4% | 24,7% |
5 | Unit EE | 29,3% | 18,2% |
Right now my rank measure looks like this:
Ranking = RANKX(ALLSELECTED(Table1[Unit]);[Column1])
Column1 = VAR Profit = CALCULATE(...) VAR Revenue = CALCULATE(...) RETURN DIVIDE(Profit;Revenue;BLANK())
Column2 = VAR Profit = CALCULATE(...)"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:
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())
Select | Column | Answer | Choice |
Extras 1 | 1 | Yes | 0 |
Extras 2 | 2 | No | 1 |
Solved! Go to Solution.
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
@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] )
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.
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
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 Thank you!
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 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!
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |