Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I am creating a new column to calculate the weighted average price for our products based on multiple filters.
I am getting what looks like a proper output when using this DAX formula.
2022 Price List Weighted average Dealer Price Column 2 = CALCULATE(DIVIDE(SUMX('quotedetail',[Dealer Price Per Unit]*[Quantity]),SUMX('quotedetail',[Quantity])),
FILTER(ALLSELECTED('quote'), quote[Price List] = "2022 Price List"),
FILTER(ALLSELECTED('quote'), quote[statuscodename] <> "Revised"))
But as soon as I add in one more filter from my Quotedetails table the amounts are all the same.
Any help with this is greatly appreciated!
Solved! Go to Solution.
I was able to resolve this by switching my relationship between table "QuoteDetail", and table "Products" to a many to many, from a many to one. Not sure why the many to one didn't work because I had direction set to both.
I was able to resolve this by switching my relationship between table "QuoteDetail", and table "Products" to a many to many, from a many to one. Not sure why the many to one didn't work because I had direction set to both.
Hey @Lindino08
The same value for all rows is because you are creating a column in quotetable and you put a filter in quote detail. There is relationship between this tables? If yes, wich type (1 to * or * to *)? Wich is the direction of the relationship (single or both). All these points can reproduce the same result for all rows.
Please share more information about your data model.
Best regards,
Marcel
Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI
Hi Marcel,
I am actually creating the new column in a table called "Products" and then filtering based on data from the "quote" table and "QuoteDetails" table. There is a one to many realationship between "Quote" and "QuoteDetail" and then a many to one relationship between "QuoteDetail" and "Products". Direction is set to both for all relationships.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
71 | |
49 |
User | Count |
---|---|
141 | |
121 | |
112 | |
59 | |
58 |