Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
yellow43
Helper I
Helper I

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
Helper I
Helper I

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.