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.
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:
Solved! Go to 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
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
@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?
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.
Hi @Anonymous
What field are you using for your report level filter?
Invoice User (same field)
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |