Calculate difference between a measure and a related column

DAX DAX Calculations
1

Hello Experts,
I am having difficulty to calculate a value.
My data set has a Fact table for Sales and SalesAgreement table with price.
I have a UnitPrice measure in Fact table.
I want to calculate the difference between this measure and Price in the SalesAgreement table.
I created a concatenated column relationship between these tables because.Concatenated column is made of 4 columns called Customer,item,type and site.

Difference = PRICE - [UnitPrice]

This is yielding me Cartesian product.

Please help me with DAX.
Thanks

2

Hi @edna ,

To avoid Cartesian product you need to expand the context of your calculation.

You can do it by using RELATED function:

Sam’s explanation in Mastering DAX course:
https://app.enterprisedna.co/app/player-course/5?chapter=189&item=543

3

For example to calculate max price difference you can use:

Max Difference = MAXX(Sales, Sales[Price]-RELATED(SalesAgreement[UnitPrice]))

image
image865×176 7.66 KB

Sample Excel and Pbix file you can find at:

PriceDiffExample.xlsx (9.3 KB)
PriceDiffExample_for_Edna.pbix (22.6 KB)

Hope it helps.
Good luck with your comparison.

4

I ended up doing everything at the SQL data base level to keep it going and deliver it.
I have created columns in a view out of both tables and used those columns in measure.
Looks good now.

Thanks for additional inputs.
Thanks

1 Like