How do I add up some rows in a matrix visual in Power BI?

[anoniem]

Hi, I have a matrix visual of a Cash Flow Statement in Power BI Desktop and the only thing I would like to do is to add a new subtotal row that is the sum of some of the rows in the matrix. Please help me, thank you!

[v-jiewu-msft]

Hi @anoniem ,

If I understand correctly, the issue is that you want to add up some rows in a matrix visual. Please try the following methods and check if they can solve your problem:

1.In the visualization pane, go to the Format tab, turn on the row subtotals.

2.You can also try to create a new table and create a new column include the sum.

3.Create the measure to sum Cost $ and Sales $.

Measuresum =
SUMX(FILTER('Table', 'Table'[Attribute]="Cost $" || 'Table'[Attribute] = "Sales $"), 'Table'[Value])

4.Create the measure to display the value.

Display Value =
SWITCH(
    SELECTEDVALUE('Table (2)'[column]),
    "Sales U", [Sales U],
    "Sales $", [Sales Amount],
    "Cost $", [Cost Amount],
    "sum", [Measuresum],
    BLANK()
)

5.Create the matrix visual, drag the Table(2) column to the Rows, drag the Date to the Columns and drag the Display value to the Values.

6.The sum row to display the Cost $ + Sales $.

Best Regards,

Wisdom Wu

[anoniem]

Dear Wisdom Wu,

I have tried your method, but I was faced with a challenge, as I created two SUMX measures, but the last step would be to combine those two (essentially your Display Value measure). Do you have any advice on how I can advice two SUMX measures?

[anoniem]

Dear Wisdom Wu,

Thank you for your reply. I have not been able to try out your solution, but I will keep you updated.

Kind regards

[PijushRoy]

Hi @anoniem 

Can you please share screenshot where you want to add subtotal in matrix visual (please hind sensitive information in SNAP)

Thanks
Pijush

[anoniem]

Dear Pijush,

Our current Cash Flow table matrix looks like this (with more columns next to it, but I simplified this screenshot):

Essentially, I would like to add a custom subtotal row in this simple matrix that is the sum of the first two subtotals in light blue, the Returns Cash Flow (i.e. Operating Cash Flow) + the Investment Indicators Cash Flow (i.e. Investing Cash Flow). This new subtotal would be the "Free Cash Flow" as shown in the second screenshot.

The table below is what I would like the cash flow table to look like. It is manually crafted by stacking the same table multiple times on top of each other, but the problem that comes from this is that the rows are not frozen once you select a different filter. This is why I am trying to find a way where I can add a solution from the inside out, rather than from outside in.

Thanks