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.
Hello!
I have dataset that contains all of our companys invoices. For internal audit purposes I need information who approves most of invoices of the specific supplier. So simplified version of our dataset is like
Supplier | Invoice number | Invoice Approver |
Supplier A | 1 | Approver A |
Supplier A | 2 | Approver B |
Supplier A | 3 | Approver C |
Supplier A | 4 | Approver A |
Supplier A | 5 | Approver B |
Supplier B | 6 | Approver C |
Supplier B | 7 | Approver A |
Supplier B | 8 | Approver B |
Supplier B | 9 | Approver C |
Supplier B | 10 | Approver A |
Supplier B | 11 | Approver B |
Supplier B | 12 | Approver A |
Supplier B | 13 | Approver A |
Supplier C | 14 | Approver B |
Supplier C | 15 | Approver C |
Supplier C | 16 | Approver A |
Supplier C | 17 | Approver C |
Supplier C | 18 | Approver C |
Supplier C | 19 | Approver A |
How do I calculate which approver approves most invoices for every supplier?
For example I want to make visualization like this:
Supplier | Most common approver | %- most common approver |
Supplier A | Approver A | 33,33 % |
Supplier B | Approver B | 33,33 % |
Supplier C | Approver C | 33,33 % |
Solved! Go to Solution.
@AleksiK , Create measures like
% approve = divide(count(Table[Approver]), calculate(count(Table[Approver]), allexpcept(Table, Table[supplier])))
Top Rank = rankx(filter(allselected(Table[Supplier], Table[Approver A]), Table[Supplier] =max(Table[Supplier])), [% approve])
And use visual level filter TOP Rank =1
@AleksiK , Create measures like
% approve = divide(count(Table[Approver]), calculate(count(Table[Approver]), allexpcept(Table, Table[supplier])))
Top Rank = rankx(filter(allselected(Table[Supplier], Table[Approver A]), Table[Supplier] =max(Table[Supplier])), [% approve])
And use visual level filter TOP Rank =1
Thank you very much! That works perfectly!
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 |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |