cancel
Showing results for
Did you mean:
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:

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]))

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)

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

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

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

## Re: Creating Ranking and Filtering to Selected Users

Hi @apierce

What field are you using for your report level filter?

Regular Visitor

## Re: Creating Ranking and Filtering to Selected Users

Invoice User (same field)

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?

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.

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.

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.

Announcements

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

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 33 members 793 guests
Recent signins: