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

Creating Ranking and Filtering to Selected Users

Good afternoon, 

 

I have been developing a KPI scorecard inspired by the Reid Havens YouTube video: 

https://www.youtube.com/watch?v=Sq4oRnCGUAE

 

I am wanting to rank our invoice users across different catagories and the calculation is working fine, but I need to be able to modify it a little. Managers and some people from outside of the department will occassionally invoice and I do not want them factored into the ranking. 

 

Here is the current calculation:

 

Invoice User Count = CALCULATE(DISTINCTCOUNT('SA Report Data Set'[Invoice User]), ALL('SA Report Data Set'[Invoice User]))
 
counter.PNG
 
This works, but includes everyone who has been an invoice user. I am wanting to reduce this to 5 selected users. 
 
What I have attempted so far:
- Have applied a report level filter for just these 5 users (doesn't make an impact on this)
- Trying ALLSELECTED instead of ALL  (I made a big mess)
 
Is there a way to use an IN operator or similar concept so I can get it to show X of 5 instead of X of 20. Thanks for any help that you may be able to provide!
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Creating Ranking and Filtering to Selected Users

Hi @apierce

 

If I understand your scenario correctly that you want to calculate the rank expect for the invoice of the Managers and some people from outside of the department. If it is, I have made an example to reproduce youe scenario.

 

Assuming that we have the sample data like this.

 

invoice user  value

u1 1000
u2 200
u3 300
u4 400
out1 1200
out2 2300
manager 460
u5 500
u6 600

 

 

Then create the two measure below.

 

Rank = RANKX(ALLSELECTED(Table1),CALCULATE(SUM('Table1'[value])),,ASC)

tag =
IF (
    SELECTEDVALUE ( Table1[invoice user] ) IN { "manger", "out1", "out2" },
    0,
    1
)

Then drag the measure tag to the visual level filer field and set like below.

set for tag.PNG

You may have a reference of my example and then check your rank measure.

 

If you still need help, please share some sample data which could reproduce your scenario and your desired output so that we could help further on it.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Super User
Super User

Re: Creating Ranking and Filtering to Selected Users

Hi @apierce

 

What field are you using for your report level filter?

apierce Regular Visitor
Regular Visitor

Re: Creating Ranking and Filtering to Selected Users

Invoice User (same field)

Super User
Super User

Re: Creating Ranking and Filtering to Selected Users

@apierce

 

Have you tried just

Invoice User Count = CALCULATE(DISTINCTCOUNT('SA Report Data Set'[Invoice User]))

 

and selecting the 5 users of your interest on the slicer?

apierce Regular Visitor
Regular Visitor

Re: Creating Ranking and Filtering to Selected Users

Thanks for the suggestion. Unfortunately, that does not get me where I need to go because I need to be able to single select the invoice user for comparison. As shown on the bottom square of ROs Invoiced, this changes the total to out of 1 as only the single invoice user is being selected on the filter.capture2.JPG

Community Support Team
Community Support Team

Re: Creating Ranking and Filtering to Selected Users

Hi @apierce

 

If I understand your scenario correctly that you want to calculate the rank expect for the invoice of the Managers and some people from outside of the department. If it is, I have made an example to reproduce youe scenario.

 

Assuming that we have the sample data like this.

 

invoice user  value

u1 1000
u2 200
u3 300
u4 400
out1 1200
out2 2300
manager 460
u5 500
u6 600

 

 

Then create the two measure below.

 

Rank = RANKX(ALLSELECTED(Table1),CALCULATE(SUM('Table1'[value])),,ASC)

tag =
IF (
    SELECTEDVALUE ( Table1[invoice user] ) IN { "manger", "out1", "out2" },
    0,
    1
)

Then drag the measure tag to the visual level filer field and set like below.

set for tag.PNG

You may have a reference of my example and then check your rank measure.

 

If you still need help, please share some sample data which could reproduce your scenario and your desired output so that we could help further on it.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 251 members 2,792 guests
Please welcome our newest community members: