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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.