cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

 

 

OperationAvg Time_UnitTotal Order QuantityLoad 
    Operation 15.5316.5 Want 21
           Item 1

1

11
           Item 210220
6 REPLIES 6
Community Support
Community Support

Hi @mtsanda 

I build a sample for you to have a test.

My table: 

1.png

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:

1.png

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. 

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 OrderOperationTotal Time
WO 1Operation 12 minutes
WO 1Operation 23 minutes
WO FutureOperation 1?
WO FutureOperation 2?

 

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

Table 2

Work OrderItemQty
Wo1Item 145
WO FutureItem 155

 

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 12 minutes * 55 pieces
Operation 23 minutes * 55 pieces

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
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/

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]

@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
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

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

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