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!
I need to summarize the value of all orders containing an item.
I made a simple model as an example
For example the total order value for ItemA (1) i want to be 1025. Because ItemA is contained i Order 1 and 2 (600 + 425).
I cant get my head around how to write the DAX formula. I need to keep the item filter to get the orderid but at the same time need to get all the orderId's including the ones that the Item relation has filtered.
Please advice 🙂
Solved! Go to Solution.
Hi @Anonymous,
Create a calculated column in 'Orders' table.
Sales per order = CALCULATE ( SUM ( Orders[Sales] ), ALLEXCEPT ( Orders, Orders[OrderId] ) )
Create a measure based on 'Item' table.
sales per item = CALCULATE ( SUM ( Orders[Sales per order] ), ALLEXCEPT ( 'Item', 'Item'[ItemName] ) )
Then, you can add 'Item'[ItemName], 'Item'[Sales] and above measure [sales per item] into table visual.
Best regards,
Yuliana Gu
Hi @Anonymous,
Create a calculated column in 'Orders' table.
Sales per order = CALCULATE ( SUM ( Orders[Sales] ), ALLEXCEPT ( Orders, Orders[OrderId] ) )
Create a measure based on 'Item' table.
sales per item = CALCULATE ( SUM ( Orders[Sales per order] ), ALLEXCEPT ( 'Item', 'Item'[ItemName] ) )
Then, you can add 'Item'[ItemName], 'Item'[Sales] and above measure [sales per item] into table visual.
Best regards,
Yuliana Gu
Thanks @v-yulgu-msft
This also did the trick:
SalesTotalOrder = CALCULATE(SUM(Orders[Sales]);ALL(Orders);VALUES(Orders[OrderId]))
I will try your measures out.
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |