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.
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])))
View solution in original post
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?
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.
Click here to read more about the November 2022 updates!
Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.