topic Re: Price Effect - incorrect Sumx calculations when applying several filters from different tables in DAX Commands and Tips

Price Effect - incorrect Sumx calculations when applying several filters from different tables

Eb50 — Wed, 29 May 2024 17:24:03 GMT

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

Re: Price Effect - incorrect Sumx calculations when applying several filters from different tables

some_bih — Thu, 30 May 2024 06:45:52 GMT

Hi Eb50 the issue is measure Price_effect?

When you see definition below there is "try to filter another measure" (part [€/Unit_Sold] or[€/Unit_Sold_LY] or [ Quantity_wo_litigation]).

In DAX this is not "easy" as measures are not columns.

This part should be rewritten to grasp DAX features.

Think what is your calculation logic / share it with input and expected output for possible solution.

MEASURE Sales[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]))

Re: Price Effect - incorrect Sumx calculations when applying several filters from different tables

Eb50 — Fri, 07 Jun 2024 15:46:25 GMT

Hi Some_bih,

Sorry for the late answer. I actually found the answer for my problem thanks to you. As I dove deeper in the data to explain better what I wanted I found out that every time I had a customer that bought the product in particular year but not the other, the result of the price effect would be 0 because of this part of the formula "IF(OR([€/Unit_Sold]=0,[€/Unit_Sold_LY]=0),0," .

I used this formula to get the correct result :

Price effect:=SUMX(SUMMARIZE(Sales;'Product'[Product_ID];Customer[Customer ID]);IF(OR([€/Unit_Sold]=0;[€/Unit_Sold_LY]=0);0;([€/Unit_Sold]-[€/Unit_Sold_LY])*[Quantity_wo_litigation])).

However, I agree with your sentence there "In DAX this is not "easy" as measures are not columns. This part should be rewritten to grasp DAX features." I just don't know how I can compare prices for a couple products & customers over different time periods doing differently. If you have an idea or a topid about it I'd be glad to hear about it !

Re: Price Effect - incorrect Sumx calculations when applying several filters from different tables

some_bih — Fri, 07 Jun 2024 17:59:00 GMT

Hi Eb50

share sample file with input and expected output for possible solution.