cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

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

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!

Roelof de Jong

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Regular Visitor

Hi @v-kkf-msft,

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

Regular Visitor

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

Community Support

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])``

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.

Community Support

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors