Forum Discussion
Dynamic Ranking Based on Date Slicer
I have a report shown below that shows the number of help desk tickets and the percentage by department for a given date range, using a date slicer. I would also like to be able to apply a dynamic rank so then I can filter the report to show the top 5 departments for the given date range. I have been struggling to get something to work using RANKX. Any help is appreciated.
I will use my model just for the example. I have claims data that has a field 'Benefit'. If I want to rank the benfit based on the % of paid it would look like this.
Benefit Rank = IF ( ISINSCOPE ( vCLAIM[Benefit] ), RANKX ( ALLSELECTED ( vCLAIM[Benefit] ), [Paid % of Total Paid] ) )I use ISINSCOPE so it doesn't give me a 1 on the total row for the rank.
Then I can apply a filter to the visual for that measure:
6 Replies
I will use my model just for the example. I have claims data that has a field 'Benefit'. If I want to rank the benfit based on the % of paid it would look like this.
Benefit Rank = IF ( ISINSCOPE ( vCLAIM[Benefit] ), RANKX ( ALLSELECTED ( vCLAIM[Benefit] ), [Paid % of Total Paid] ) )I use ISINSCOPE so it doesn't give me a 1 on the total row for the rank.
Then I can apply a filter to the visual for that measure:
That worked great. Thank you.
jdbuchanan71 My count and percentage are both measures. When I filter to return the top 5 I'd like my percentage to be of the total count for the date range and not just a percentage of the top 5. In your example above, when you apply the filter to only show the top 5, I'd want the percentage for Hosptial Inpatient to still be 15.53%. Thanks again for the help.
You will have to modify your % measure to use ALLSELECTED probably.
jdbuchanan71 Here is what I have
Count = COUNT('servicedesk BIA_tickets'[request_number])SelectedTotal = CALCULATE('servicedesk BIA_tickets'[Count],ALLSELECTED('servicedesk BIA_tickets'))PercentageOfSelected = DIVIDE('servicedesk BIA_tickets'[Count],'servicedesk BIA_tickets'[SelectedTotal])PercentageOfSelected is what I am using in the table.
When you are referencing a measure in another measure you shouldn't include the table name.
'servicedesk BIA_tickets'[Count] = looks like the [Count] column on the 'servicedesk BIA_tickets'. Instead you should write it like this.
PercentageOfSelected = DIVIDE ( [Count], [SelectedTotal] )That being said, you can adjust your PercentageOfSelected like this.
PercentageOfSelected = VAR _AllTickets = CALCULATE ( [Count], REMOVEFILTERS ( 'servicedesk BIA_tickets'[Department] ) ) RETURN DIVIDE ( [Count], _AllTickets )That same pattern worked for my example with this test measure.
Paid % of total = VAR AllClaims = CALCULATE ( [Paid Amount], REMOVEFILTERS ( vCLAIM[Benefit] ) ) RETURN DIVIDE ( [Paid Amount], AllClaims, 0 )
