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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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