cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ABMN
Regular Visitor

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.

 

ABMN_0-1659023785644.png

 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

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.

jdbuchanan71_0-1659024994211.png

Then I can apply a filter to the visual for that measure:

2022-07-28_9-17-53.jpg

 

View solution in original post

6 REPLIES 6
jdbuchanan71
Super User
Super User

@ABMN 

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 )

jdbuchanan71_0-1659041394723.png

 

jdbuchanan71
Super User
Super User

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.
ABMN
Regular Visitor

@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.

jdbuchanan71
Super User
Super User

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.

jdbuchanan71_0-1659024994211.png

Then I can apply a filter to the visual for that measure:

2022-07-28_9-17-53.jpg

 

That worked great.  Thank you.

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.