Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Eb50
Frequent Visitor

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 : 

 

Eb50_2-1717002592456.png

 

 

Eb50_1-1717002529038.png

 

However, whenever I want to add a filter on the customer (customer category), the total is still correct but not the detail by customer :

 

Eb50_3-1717002728970.png

 

To explain a bit further how everything is tied together, the data model has 4 tables that look like this :

 

Eb50_0-1717001593442.png

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

1 ACCEPTED SOLUTION
Eb50
Frequent Visitor

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 !

View solution in original post

3 REPLIES 3
some_bih
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Eb50
Frequent Visitor

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 !

Hi @Eb50 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.