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 Community,
I have a question regarding how to calculate a percentage from Total Amount with different categories.
Please check below a small example of what I want to calculate:
I would like to have a matrix, but my issue is to calculate the formula for the percentage. The percentage showned on the table is, for instance,
Situation A = Store A (15 units) / Seller Anne (180) = 8%
How can I calculate this formula? I have tried to create a new measure with ALLEXCEPTED and ALL, but returns the percentage with total from all sellers (210).
Thank you in advance for your help.
Solved! Go to Solution.
divide(Sum(table[Qty]), calculate(Sum(table[Qty]), removefilters(Table[Country], Table[Store])))
divide(Sum(table[Qty]), calculate(Sum(table[Qty]), allexcept(Table, Table[seller])))
divide(Sum(table[Qty]), calculate(Sum(table[Qty]), filter(allselected(Table) , Table[seller] = max(Table[Seller])))
Hello again @amitchandak,
Your formulas work, but if I apply a Visual-level Filters in the Dashboad Report to only show store A and store D. All % will change. How can I fix the percentage value to always remain the same, although I apply a filter?
divide(Sum(table[Qty]), calculate(Sum(table[Qty]), removefilters(Table[Country], Table[Store])))
divide(Sum(table[Qty]), calculate(Sum(table[Qty]), allexcept(Table, Table[seller])))
divide(Sum(table[Qty]), calculate(Sum(table[Qty]), filter(allselected(Table) , Table[seller] = max(Table[Seller])))
Hello @amitchandak,
Thank you for your reply. It worked!!
One last question: how can I apply a filter where it will show only the stores that have a % greater than 40%?
In this case, it should only show 3 stores: Store B (44%), Store A (42%) and Store D (90%).
Because if I apply a Visual-level Filters on Dashboad Report, it will apply to the seller level, and I want to apply to Store Level.
Thank you in advance.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |