Forum Discussion

bgirish's avatar
bgirish
Frequent Visitor
8 years ago
Solved

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, ite...
  • alexei7's avatar
    alexei7
    8 years ago

    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

bgirish's avatar
bgirish
Frequent Visitor
8 years ago
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.

 

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
8 years ago

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