cancel
Showing results for
Did you mean:

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

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:

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

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
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?

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

Helper I

Hello @amitchandak

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors