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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bgirish
Frequent Visitor

Summning data from different rows

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.

 

1 ACCEPTED SOLUTION
alexei7
Continued Contributor
Continued Contributor

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

View solution in original post

4 REPLIES 4
LaurentCouartou
Solution Supplier
Solution Supplier

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.

 

Capture.PNG

 

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
alexei7
Continued Contributor
Continued Contributor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.