Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
RoelofdeJong
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):

 

RoelofdeJong_0-1641726092527.png

 

I want to achieve two things here:

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

RoelofdeJong_1-1641726488296.png

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

RoelofdeJong_2-1641726773442.png

 

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

RoelofdeJong_3-1641726995742.png

RoelofdeJong_4-1641727083969.png

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

 

 

 

 

 

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

Hi @RoelofdeJong ,

 

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

 

vkkfmsft_0-1641952465074.png

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]

vkkfmsft_1-1641952575461.png

 

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.

View solution in original post

4 REPLIES 4
RoelofdeJong
Regular Visitor

Hi @v-kkf-msft,

 

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

RoelofdeJong
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:

RoelofdeJong_0-1642090347507.png

 

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:

RoelofdeJong_1-1642090503660.png

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:

RoelofdeJong_2-1642091044806.png

 

while I get the values 10 and 11

RoelofdeJong_3-1642091112461.png

 

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

Hi @RoelofdeJong ,


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

vkkfmsft_1-1642150851283.png

Screenshot 2022-01-14 164306.png  Screenshot 2022-01-14 164629.png

 

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.

 

 

v-kkf-msft
Community Support
Community Support

Hi @RoelofdeJong ,

 

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

 

vkkfmsft_0-1641952465074.png

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]

vkkfmsft_1-1641952575461.png

 

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.