Forum Discussion
Matrix Adding total from previous Group
Hi Everyone,
I am facing an issue while calculating totals from the previous group. Currently, I am able to get the total for each group individually, but I need to retrieve the previous group’s total and add it to the current group.
Please find the attached screenshot for your reference.
Kindly advise on how to achieve this.
Hi nagendranath,
Thank you for reaching out to Microsoft Fabric Community Forum.
The requirement is for a cumulative or running total across groups, not just a standard group subtotal.For instance:
- Total Machinist = 475
- Total Machinist Including Trainee = 475 + 13 = 488
This means each subsequent group should include the totals from the preceding groups.
This result can't be achieved with only Matrix grouping or subtotal options. Instead, you should use a Running Total measure in DAX.
First Create an Order column
Assign a numeric order to your groups so Power BI can determine the sequence.
Example:
GroupOrderMachinist1Machinist Including Trainee2Supervisor3Supervisor Including Trainee4
Ensure the Group column is sorted by the Order column.
Next Build the Running Total MeasureRunning Total =
VAR CurrentRank =
MAX ( 'Table'[Order] )
RETURN
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table'[Group], 'Table'[Order] ),
'Table'[Order] <= CurrentRank
)
)Later Apply the Measure in Matrix
Replace the current total measure in your Matrix visual with this new Running Total measure.
The result will be:
- Machinist = 475
- Machinist Including Trainee = 488
- Each following group = Previous total + Current group total
This method offers flexibility and allows you to rank groups and carry totals forward dynamically.
Thank you.
8 Replies
Hi nagendranath,
You can use inscope() function to achieve this:-
Present Count =
SUM(Table[Present])Present Custom Total =
VAR CurrentGroup =
SELECTEDVALUE(Table[Group])VAR CurrentValue =
[Present Count]VAR PreviousGroupsTotal =
CALCULATE(
[Present Count],
FILTER(
ALL(Table[Group]),
Table[GroupOrder] <
MAX(Table[GroupOrder])
)
)RETURN
IF(
ISINSCOPE(Table[Nature of Work]),
-- Detail rows
CurrentValue,-- Subtotal rows
CurrentValue + PreviousGroupsTotal
)If this doesn't fix it, kindly share the DAX which you are using with sample data.
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!The eassiest way is to use groups for example : (MACHINEST & SMS MACHINEST) In one group and so on
It will group those into one Group but for the next group i need this total. Which is not possible if i group.
You Can make Sub_Group when you make grouping as the attached photo ,
I attached for you how should the grouping be like with some sitting along to have the result as you desire
After Grouping make this Setting:
01st make the Layout as Tabuar:
02nd Setup the Row Subtotals Setting:
The End Result:
Best of luck
I have tried but its not adding up. Please find the attached aboveHi nagendranath,
Thank you for reaching out to Microsoft Fabric Community Forum.
The requirement is for a cumulative or running total across groups, not just a standard group subtotal.For instance:
- Total Machinist = 475
- Total Machinist Including Trainee = 475 + 13 = 488
This means each subsequent group should include the totals from the preceding groups.
This result can't be achieved with only Matrix grouping or subtotal options. Instead, you should use a Running Total measure in DAX.
First Create an Order column
Assign a numeric order to your groups so Power BI can determine the sequence.
Example:
GroupOrderMachinist1Machinist Including Trainee2Supervisor3Supervisor Including Trainee4
Ensure the Group column is sorted by the Order column.
Next Build the Running Total MeasureRunning Total =
VAR CurrentRank =
MAX ( 'Table'[Order] )
RETURN
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table'[Group], 'Table'[Order] ),
'Table'[Order] <= CurrentRank
)
)Later Apply the Measure in Matrix
Replace the current total measure in your Matrix visual with this new Running Total measure.
The result will be:
- Machinist = 475
- Machinist Including Trainee = 488
- Each following group = Previous total + Current group total
This method offers flexibility and allows you to rank groups and carry totals forward dynamically.
Thank you.
Hi nagendranath,
we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.
Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!
