Price Effect - incorrect Sumx calculations when applying several filters from different tables
Hi everyone, I looked on the forum before posting but I coudn't get a resultat that works for my situation. I'd gladly use your help on this because I have been stuck for almost a month now. I'm trying to calculate a price effect on PowerPivot in a measure but the result isn't always correct. When displayed in a Pivot Table with only the products it gives me a result that seem to be correct. The calculation on each product row is correct, and the total is an addition of all my results. It look like this : However, whenever I want to add a filter on the customer (customer category), the total is still correct but not the detail by customer : To explain a bit further how everything is tied together, the data model has 4 tables that look like this : The relations are [Product]Product ID --> Sales[Product ID], [customer]Customer ID --> Sales[Customer_ID], and [calendar]Date --> [Sales]Date. Inside the model I have a few calculations : The first one calculate a turnover without the litigation amount (when there is no cause, there is no litigation with the customer) Gross turnover:=CALCULATE(SUM(Sales[Turnover]);Sales[Litigation cause]="") The seconde one is the same but give the Last Year result : Gross turnover_LY:=CALCULATE([Gross turnover];SAMEPERIODLASTYEAR('Calendar'[Date])) Then, I do exactly the same for the quantity : Quantity_wo_litigation:=CALCULATE(SUM(Sales[Quantity]);Sales[Litigation cause]="") Quantity_wo_litigation_LY:=CALCULATE([Quantity_wo_litigation];SAMEPERIODLASTYEAR('Calendar'[Date])) With those 4 measure, I'm able to compute the € per unit sold : €/Unit_Sold:=[Gross turnover]/[Quantity_wo_litigation] €/Unit_Sold_LY:=[Gross turnover_LY]/[Quantity_wo_litigation_LY] And in the end, here's my calculation that I have a problem with : Price effect:=SUMX(VALUES('Product'[Product_ID]);IF(OR([€/Unit_Sold]=0;[€/Unit_Sold_LY]=0);0;([€/Unit_Sold]-[€/Unit_Sold_LY])*[Quantity_wo_litigation])) I think there is probably something that I still don't really understand about Row context, filter context and context transition, That prevents me from achieving what I want ... I have to add that in the sales table, an article can be sold at 2 different customers for a different price. Thus I'm even starting to doubt that VALUES(VALUES('Product'[Product_ID])) even gives me a correct answer when I don't apply a filter on customer. Here is the link to the excel file if you want to take a closer look : Price_effect Thank you very much
