Forum Discussion
Dynamic Table Rank - how to get this to work?
I've been trying to use the rank function such as: Rank with Visuals applied
However, when I apply this to my PBI my ranks go from 1 to 1700 or so (depending on what's in the table). However, since there are only a few VPs I would like it to be 1,2,3,4..... And ranked by VP. My table that has VP's in it is DimPersonnel, which I'm not using below...
Below is my calculation:
Rank = RANKX(ALLSELECTED('FactSales') ,ROUND(CALCULATE(AVERAGE(FactSales[Aging]),
DATESYTD('FactSales'[CaseCloseDate]),
FILTER ( FactSales, AND ( FactSales[Scorecard Bucket] IN { "FILTERS"}, ( FactSales[OpenClosed] IN{"Closed" } )
))),1))
Hi Anonymous
Try the below
Rank = VAR tbl = CALCULATETABLE( VALUES(FactSales[Aging]), ALLSELECTED() ) RETURN RANKX( tbl, ROUND( CALCULATE( AVERAGE(FactSales[Aging]), DATESYTD(FactSales[ClosedDate]), FILTER ( FactSales, AND ( FactSales[Scorecard Bucket] IN { "FILTERS"}, FactSales[OpenClosed] IN {"Closed" } ) ) ), 1 ),,, Dense )
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
v-cherch-msftMicrosoft Employee
Hi Anonymous
Sample data and expected output will be helpful to provide an accurate solution.If you need further help,please follow the How to Get Your Question Answered Quickly to post your simple assumed data and expected output.It would be better if you can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.
Regards,
Thank you for the tip v-cherch-msft
Please find some dummy data below. I hope this is enough, I trimmed it down a lot but these are the basics. As you already probably know this table has a few thousand records in it.
DimPersonnel
Id VP 10 VP Bill 20 VP Bob 30 VP Jim 40 VP Jill 50 VP Janet 60 VP Somebody 70 VP Someone Else 80 VP Homer 90 VP Marge 100 VP Bart FactSales
PRSN_Driver_Name__c ClosedDate Aging Scorecard Bucket 10 1/7/2019 26 FILTER A 20 1/2/2019 14 FILTER B 30 1/30/2019 23 FILTER A 40 1/22/2019 15 FILTER B 50 1/22/2019 11 FILTER A 60 1/21/2019 10 FILTER B 70 1/18/2019 1 FILTER A 80 2/19/2019 14 FILTER B 90 2/21/2019 0 FILTER A 100 3/5/2019 1 FILTER B wanted results:
PRSN_Driver_Name__c ClosedDate Aging Scorecard Bucket RegionVP__c Rank what I typicaly see today 90 2/21/2019 0 FILTER A VP Marge 1 1 70 1/18/2019 1 FILTER A VP Someone Else 2 100 100 3/5/2019 1 FILTER B VP Bart 2 100 60 1/21/2019 10 FILTER B VP Somebody 4 1671 50 1/22/2019 11 FILTER A VP Janet 5 etc… 20 1/2/2019 14 FILTER B VP Bob 6 80 2/19/2019 14 FILTER B VP Homer 7 40 1/22/2019 15 FILTER B VP Jill 8 30 1/30/2019 23 FILTER A VP Jim 9 10 1/7/2019 26 FILTER A VP Bill 10
- Mariusz
Community Champion
Hi Anonymous
Make sure you use Dense as 4th argument in your RANKX expression
RANKX(,,,DENSE)
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks Mariusz ,
That almost works. Now instead of into the thousands it's down to 20's. Can you tell me if there's a way to have it just show rank on what is showing in the power bi visual? So if only 3 VP's from above are in the visual I would see 1, 2, 3 even if #3 was the last one out of all of the VP's.
- Mariusz
Hi Anonymous
Try the below
Rank = VAR tbl = CALCULATETABLE( VALUES(FactSales[Aging]), ALLSELECTED() ) RETURN RANKX( tbl, ROUND( CALCULATE( AVERAGE(FactSales[Aging]), DATESYTD(FactSales[ClosedDate]), FILTER ( FactSales, AND ( FactSales[Scorecard Bucket] IN { "FILTERS"}, FactSales[OpenClosed] IN {"Closed" } ) ) ), 1 ),,, Dense )
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.