Skip to main content
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.

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 : 







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



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 :




Thank you very much

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!

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

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.