Forum Discussion
Pricing Difference
Hi,
How do I go about devising a formula to calculate the difference in specific customer product pricing between 2 periods in DAX?
Based on the table below, the pricing impact for Product X will be:
Product X impact for Customer A = ($1.50-$2.00)*2,000 = $-1,000
Product X impact for Customer B = ($2.50-$2.40)*800 = $80
Total price impact for Product X in Period 2 will be -1,000+80 = -920
| Period | Customer | Product | Price | Volume |
| Jan-21 | Customer A | Product X | $ 2.00 | 1,000 |
| Jan-21 | Customer A | Product Y | $ 2.50 | 500 |
| Jan-21 | Customer B | Product X | $ 2.40 | 400 |
| Jan-21 | Customer B | Product Y | $ 2.30 | 800 |
| Feb-21 | Customer A | Product X | $ 1.50 | 2,000 |
| Feb-21 | Customer A | Product Y | $ 3.00 | 200 |
| Feb-21 | Customer B | Product X | $ 2.50 | 800 |
| Feb-21 | Customer B | Product Y | $ 2.00 | 400 |
Thanks
Hanson
5 Replies
- ERD
Community Champion
Hi Anonymous ,
I'm not aware of the resulting visual you needm but you can try this measure:
Impact = VAR _t = ADDCOLUMNS ( SUMMARIZE ( T, T[Product], T[Customer], T[Period] ), "@impact", VAR c_price = CALCULATE ( MAX ( T[Price] ) ) VAR p_price = CALCULATE ( MAX ( T[Price] ), FILTER ( ALL ( T[Period] ), T[Period] < MAX ( T[Period] ) ) ) RETURN CALCULATE ( ( c_price - p_price ) * MAX ( T[Volume] ) ) ) RETURN SUMX(_t, [@impact])If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
Anonymous
And what will you try to measure when more data arrives? Still only hard-coded Jan-Feb prices? Or rather you want this to be a dynamic calculation and you want to really be able to compare any one month to the previous one? Power BI has been designed to be flexible and work for any correct input data, not only for one case.
