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
mtsanda
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
v-rzhou-msft
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. 

DataZoe
Employee
Employee

@mtsanda

Try this measure:

 

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

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

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.