Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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 :
Thank you very much
Solved! Go to Solution.
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 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]))
Proud to be a Super User!
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.
Proud to be a Super User!
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
8 |
User | Count |
---|---|
30 | |
26 | |
15 | |
13 | |
10 |