cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Dax formula output breaks when filtering on multiple tables

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.

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"),
FILTER(ALLSELECTED('quotedetail'), quotedetail[Option Upcharge?] = "No"))

Any help with this is greatly appreciated!

Super User

@Lindino08 , Use allselected , if you want to ignore some filter. Try like

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"),
FILTER(('quotedetail'), quotedetail[Option Upcharge?] = "No"))

or

2022 Price List Weighted average Dealer Price Column 2 = CALCULATE(DIVIDE(SUMX('quotedetail',[Dealer Price Per Unit]*[Quantity]),SUMX('quotedetail',[Quantity])),
FILTER(('quote'), quote[Price List] = "2022 Price List" && quote[statuscodename] <> "Revised"),
FILTER(('quotedetail'), quotedetail[Option Upcharge?] = "No"))

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.