cancel
Showing results for
Search instead for
Did you mean:
New Member

## Sum rows for subtotal

I want my subtotal for my matrix to sum the rows below it. How can I do it? Currently load is calculated as Avg Time_Unit * Order_Quantity

 Operation Avg Time_Unit Total Order Quantity Load Operation 1 5.5 3 16.5 Want 21 Item 1 1 1 1 Item 2 10 2 20
6 REPLIES 6
Community Support

Hi @mtsanda

I build a sample for you to have a test.

My table:

I build two measures:

``````Measure =
VAR _Avg = AVERAGE('Table'[Time])
VAR _Sum = SUM('Table'[Order])
VAR _Load = _Avg*_Sum
Return
_Load``````
``Load = IF(ISINSCOPE('Table'[Item]),[Measure],SUMX ('Table', [Measure]))``

Result is as below:

You can download the pbix file from this link: Sum rows for subtotal

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

New Member

Thank you for all the help so far. I think I am leaving out information that may have be messing everything up. I have two tables.

The first table contains historical work orders, operations, and total times for those operation. From this table I am trying to essentially build a lookup table for average part time per operation for the item (which is located in a different table)

Table 1

 Work Order Operation Total Time WO 1 Operation 1 2 minutes WO 1 Operation 2 3 minutes WO Future Operation 1 ? WO Future Operation 2 ?

This table contains past and future WO orders. It has information about order qty, and the item.

Table 2

 Work Order Item Qty Wo1 Item 1 45 WO Future Item 1 55

I am trying to get the final result using only measures as to not slow up my program. I have been averaging historical times and multiplying them by future work order quantities. At the item level it seems to work, but at the operation level I get stuck with the first issue mention. Does this new information bring any thoughts to the table?

Matrix

 Operation Load = Average(Past_times)*Current work Qty for item number Operation 1 2 minutes * 55 pieces Operation 2 3 minutes * 55 pieces
Community Support

Hi @mtsanda

I couldn't see item2 in your second sample, and it seems to be important.

Could you show me more details about your two tables?

Please tell me how did you relate two tables? Did you build relationships by work order column?

And you can show me your measures and the screenshot of your result.

This may make it easier for me to understand your data model.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User II

@mtsanda

Try this measure:

Current Load =
SUMX ( 'Table', 'Table'[Avg Time_Unit] * 'Table'[Order Quantity] )

Respectfully,
DataZoe

See my reports and blog at https://www.datazoepowerbi.com/

New Member

That gives me wrong values, but does sum the up. I think this has something to do with me using two measures and not referencing columns. This is my load calculation.

Load = [Time_Unit] * [Total Order Quantity]
Super User II

@mtsanda Did you try it using the columns with the values?

Respectfully,
DataZoe

See my reports and blog at https://www.datazoepowerbi.com/

## Helpful resources

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!

#### Check it Out!

Click here to read more about the December 2020 Updates!

#### Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

#### Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors