Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to Solution.
Hi @RoelofdeJong ,
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.
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
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])
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.
Hi @RoelofdeJong ,
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
43 | |
31 | |
24 | |
22 | |
22 |