cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yellow43
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:

 

yellow43_0-1664401084668.png

 

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
amitchandak
Super User
Super User

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

View solution in original post

3 REPLIES 3
yellow43
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?

amitchandak
Super User
Super User

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

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.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors