Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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 )
You will have to modify your % measure to use ALLSELECTED probably.
@jdbuchanan71 Here is what I have
@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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |