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 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!

 

1 Reply

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