Forum Discussion
Solved
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 r...
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:
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 )