Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙂
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 very clear, You can use Rank and filter it.
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
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
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |