Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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.
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 |
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.
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.
@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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |