topic Re: Price difference of identical products across different companies - min max values in DAX Commands and Tips

Price difference of identical products across different companies - min max values

Anonymous — Sun, 09 Jan 2022 11:19:29 GMT

Hi all,

I have a question regarding comparing prices of identical items. I did not find the right answer yet on this forum. I will explain my case.

I want to compare purchase prices of different companies based on the global product ID. Here is an example of a table (dummy data):

I want to achieve two things here:

1. Create a matrix of the pricing of the same global product of the different companies

2. Calculate the min and max values of these records, the difference and calculate the ordered quantity from the purchase orders:

I have the following model (three different tables) and the relations

The combined key is the company code + the local product ID (to make them unique).

I hope it is clear and I will get an answer on this case soon!

Much appreciated and many thanks!

Thanks in advance/

Roelof de Jong

Re: Price difference of identical products across different companies - min max values

v-kkf-msft — Wed, 12 Jan 2022 01:57:04 GMT

Hi Anonymous ，

Not knowing your exact data, so I created the following simple example.

Price = SUM ( Fact_Purchase_Orders[Price in EUR] )

Min = MINX ( SUMMARIZE ( Fact_Purchase_Orders, Dim_Product_Global[Global Product Code], Dim_Product_Local[Product Company Code] ), [Price] )

Max = MAXX ( SUMMARIZE ( Fact_Purchase_Orders, Dim_Product_Global[Global Product Code], Dim_Product_Local[Product Company Code] ), [Price] )

Difference = [Max] - [Min]

Ordered quantity = COUNTROWS ( Fact_Purchase_Orders )

QTY*difference = [Difference] * [Ordered quantity]

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Price difference of identical products across different companies - min max values

Anonymous — Thu, 13 Jan 2022 16:25:34 GMT

Hi v-kkf-msft,

Thanks for your answer. This was already really useful. Much appreciated. However, I do not get the correct outcome yet.

I have implemented the measures and I have now this:

However, when I check the purchase order data, the min / max values are incorrect. See for example the AP000000006522 code when I look in the purchase order data:

I shoul expect of all these values the min value of 2.97 and the max value 3.28

When I also look at your PBIX, I see that the figures are not right. So I am expecting this for product x as min / max values:

while I get the values 10 and 11

So we are close by but not there yet.

What the next step would be (additional request) is to see over time how the price evolves

Re: Price difference of identical products across different companies - min max values

v-kkf-msft — Fri, 14 Jan 2022 09:11:03 GMT

Hi Anonymous ,

If you want to keep only the filter on the [Global Product Code] column, please try the following measures.

Min = CALCULATE ( Min ( Fact_Purchase_Orders[Price in EUR] ), filter ( ALL ( Fact_Purchase_Orders ), Fact_Purchase_Orders[Global Product Code] = MAX ( Fact_Purchase_Orders[Global Product Code] ) ) )

Max = CALCULATE ( MAX ( Fact_Purchase_Orders[Price in EUR] ), filter ( ALL ( Fact_Purchase_Orders ), Fact_Purchase_Orders[Global Product Code] = MAX ( Fact_Purchase_Orders[Global Product Code] ) ) )

If you want to calculate the min\max value in the column based on context, please try the following measure.

_max = MAX(Fact_Purchase_Orders[Price in EUR])

_min = MIN(Fact_Purchase_Orders[Price in EUR])

Re: Price difference of identical products across different companies - min max values

Anonymous — Fri, 14 Jan 2022 10:08:11 GMT

Hi @v-kkf-msft,

This was indeed the solution. Many thanks and much appreciated!