Creating Ranking and Filtering to Selected Users

[Anonymous]

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

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!

[v-piga-msft · Accepted solution]

Hi @Anonymous，

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

[AlB]

@Anonymous

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?

[Anonymous]

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.

[AlB]

Hi @Anonymous

What field are you using for your report level filter?

[Anonymous]

Invoice User (same field)