Forum Discussion

Lindino08's avatar
Lindino08
Frequent Visitor
3 years ago

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 Pr...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

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