I search this forum with many different keywords but still cannot get to solution yet, totally no idea how to tackle this.
The complex part is that the value should be responsive to all present filters, so calculated column cannot achieve the result, believe it has to be dynamic measures.
Sample data like this:
Visual A is total spend amount by vendor (shows that Vendor B & Vendor A are contributed to Top 80% of spend amount).
Visual B (spend/inv count by product), C1 and C2 (inv count based on amount range) are calculated based on Top 80% Spend Vendors.
I only managed to achieve visual A, but unable get the result for visual B, C1 and C2.
Any help would be very much appreciated.
Thanks in advance.
Try on this dax but didn't get the correct result. Can anyone helps?
Your question is an variant of common Pareto analysis; but I'm not surprised that you can't solve it after a deep look into it. Its intricacy is way more than it appears. It's way beyond most users.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!
DAX is simple, but NOT EASY!
I gather Dynamic ABC Classification from this post might be helpful but I just don't know how to fit it to my case.
ABC classification – DAX Patterns
Can you enlighten us how you got Visual B ???, especially Spent Amount(Top 80% Spend Vendors) more explicitly.
Hi, thanks for replying to my question.
Vendors are ranked/sorted based on total spend amount, from largest total amount ranked as first to smallest. Based on the sorted vendor then calculate the cumulative percentage. Lastly, vendors with cumulative percentage from 0% up to 80% are categorised as Top 80% spend vendors. (this is what you can see from visual A).
From the sample data given above, only vendor A and B are within cumulative 80% (vendor D & C already exceeded 80% so out of scope).
So, visual B is the total amount and invoice count by product for Top 80% spend vendors, which is vendor A and B only. This two vendors contributed 3 products (Product1, Product2 and Product3).
Below is example how to get the total amount ($375) and invoice count (4 invoices) for Product1.
I hope this helps.
Check out the changes to the Power BI Community announced at Build.
Find out more about the May 2023 update.
Visit our Data Stories Gallery and give kudos to your favorite Data Stories.