topic Re: Percentage from Total Amount with Several Categories in DAX Commands and Tips

Percentage from Total Amount with Several Categories

yellow43 — Wed, 28 Sep 2022 21:42:49 GMT

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.

Re: Percentage from Total Amount with Several Categories

amitchandak — Thu, 29 Sep 2022 02:13:29 GMT

yellow43 ,

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])))

Re: Percentage from Total Amount with Several Categories

yellow43 — Thu, 29 Sep 2022 09:17:24 GMT

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.

Re: Percentage from Total Amount with Several Categories

yellow43 — Thu, 29 Sep 2022 09:34:22 GMT

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?