Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all
Hope you can help. I am working with some data as below. I want to create a calculated column that brings back the largest product category by value in an order (made up of various items). The categories are held in a separate table by SKU (Table 1). Table 2 is an example of the order data.
I want to assign the maximum value category in the order and identify the order as a sale of that category.
Any help very much appreciated!
Thanks
Jack
Solved! Go to Solution.
Hi @EnviroJack
You may try below measure:
Column = LOOKUPVALUE ( 'Table 1'[Group], 'Table 1'[Lineitem Sku], CALCULATE ( MAX ( 'Table 2'[Lineitem Sku] ), TOPN ( 1, FILTER ( 'Table 2', 'Table 2'[Order] = EARLIER ( 'Table 2'[Order] ) ), 'Table 2'[Lineitem amount], DESC ) ) )
Regards,
Cherie
Hi @EnviroJack
You may try below measure:
Column = LOOKUPVALUE ( 'Table 1'[Group], 'Table 1'[Lineitem Sku], CALCULATE ( MAX ( 'Table 2'[Lineitem Sku] ), TOPN ( 1, FILTER ( 'Table 2', 'Table 2'[Order] = EARLIER ( 'Table 2'[Order] ) ), 'Table 2'[Lineitem amount], DESC ) ) )
Regards,
Cherie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |