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.
I've got a table called 'orders'
order_id, customer_id
1001, 5000
1002, 5432
and another 'order_detail' with multiple rows per order
order_detail_id, order_id, type, value
1, 1001, item, 50.00
2, 1001, item, 25.99
3, 1001, subtotal, 75.99
4, 1001, discount, 5.00
5, 1001, discount, 2.00
6, 1001, tax, 20.00
7, 1001, total, 90.99
I want to add a calculated colum 'revenue' to the 'orders' table.
Effectively it would be sum of the related items MINUS the sum of the related discounts
I'm new to this and any help will be appreciated.
Thank you.
Solved! Go to Solution.
Hi @bgirish,
Presuming you have the two tables joined by order_id in the relationships view, try the following:
Revenue = CALCULATE(SUM(order_detail[value]),order_detail[type]="item")-CALCULATE(SUM(order_detail[value]),order_detail[type]="discount")
Hope that helps,
Alex
In the Orders table, you could add something like:
CALCULATE( SUM( order_detail[value] ) , order_detail[type] = "item" ) - CALCULATE( SUM( order_detail[value] ) , order_detail[type] = "discount" )
Edit: in case you require additional calculated columns, you may want to write the following instead (to avoid circular reference errors).
CALCULATE( SUM( order_detail[value] ) , order_detail[type] = "item" , ALLEXCEPT( orders, orders[order_id] ) ) - CALCULATE( SUM( order_detail[value] ) , order_detail[type] = "discount" , ALLEXCEPT( orders, orders[order_id] ) )
Revenue = CALCULATE(SUMX(FILTER('order_detail', 'order_detail'[type]="item"), 'order_detail'[linetotal]))
-
CALCULATE(SUMX(FILTER('order_detail', 'order_detail'[type]="discount"), 'order_detail'[linetotal]))
This is what I ended up using. I couldn't get SUM to work, but I did use SUMX.
Thanks for steering me in the right direction.
Hi @bgirish,
By my tests, the replies from others could solve your problem by creating a calculated column to get the MINUS of sum of the related items and the sum of the related discounts.
However, It seems that you still need help, could you please share your expected output so that we can help further investigate on it?
In addition, what is the 'order_detail'[linetotal] in your formula above? Please describe your scenario in more details.
Best Regards,
Cherry
Hi @bgirish,
Presuming you have the two tables joined by order_id in the relationships view, try the following:
Revenue = CALCULATE(SUM(order_detail[value]),order_detail[type]="item")-CALCULATE(SUM(order_detail[value]),order_detail[type]="discount")
Hope that helps,
Alex
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 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |