cancel
Showing results for
Did you mean:
Frequent Visitor

## Percentage from Total Amount with Several Categories

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.

1 ACCEPTED SOLUTION
Super User

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

3 REPLIES 3
Frequent Visitor

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?

Super User

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

Frequent Visitor

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.