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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate weighted average that only applies specific filters

Hi everyone,

 

I'd like to find a way to calculate weighted average, which is affected only by a given filter.

 

Here's the example data:

 

Customer

Top Customer?

Product

Cost

Volume

Cust 1

yes

Bike

1

100

Cust 2

yes

Bike

2

50

Cust 2

yes

Car

3

200

Cust 3

no

Car

4

300

Cust 4

no

Car

5

50

 

First, I'd like to calculate a weighted average for all of the cutstomers (both Top Customers and Not). I managed to do that by using "ALL" formula - whether I then filter by Top Customers or not, the average stays the same.

 

However, my next step is to calculate  this average by product. What I want to achieve is that the average IS NOT AFFECTED by the "Top Customer" filter, but IS AFFECTED by the "Product" filter.

 

I think the "ALL EXCEPT" formula can be a solution here, however I'm not able to combine it with the weighted average quick measure. It keeps giving me errors.

 

Do you have any idea on how to solve it? Any help will be highly appreciated 🙂

4 REPLIES 4
dax
Community Support
Community Support

Hi @Anonymous , 

You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

Hi Zoe,

 

Thank you for your help. I checked the attached file - however, I see that the "Top customer" filter still influences the measures. I don't think this is te solution I was looking for unfortunately.

Anonymous
Not applicable

Dear Amit, thank you for your answer, unfortunately I don't think this is what I need. I'm just trying to use the quick measure - weighted average per category, but set it so that the "Top Customer" filter does not affect that measure. Do you have any idea how to approach this?

 

Thank you

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.