How to add a measure to a row of a matrix that is predefined?

[Sagejah9]

I am preparing a PnL using the matrix visual in PowerBI with Months as Columns and Line Items as rows nested. All the data is coming from one table and all group names and line items are in the right order. The rows that are blank require calculations but the line names exist in the table. No values. I tried using a switch case but the measure returns blank. This a technique I am seeing for creating custom subtotal. Any Help, please?

[v-xulin-mstf]

Hi @Sagejah9,

Try measure as:

Measure =

IF(

   HASONEFILTER(Table1[GroupName]),

   SUM(Table1[Amount]),

   [Total Revenue]-[Total Expenses])

And then modify the name of ‘subtotal’:

Here is the output:

Here is the demo, please try it: How to add a measure to a row of a matrix that is predefined?

Best Regards

Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Sagejah9]

This is great, thanks! but how can I keep the Row totals for Revenue and Expense  and used the NetProfit row that's already in the table, just that its values are either blank or 0, I would like to replace this with a measure. So, like going through row items (Groupnames) and allocating that measure when necessary.    

[v-xulin-mstf · Accepted solution]

Hi @Sagejah9,

According to you expected output, may be you can not do this.

It will  pivot all the table when you drop column into values.

As:

Best Regards,

Link

[vanessafvg]

can you share the calculation you are using and show with some visuals what you want to see.

[Sagejah9]

I made a sample PnL in excel to show the structure in the matrix visual and where I would like the calculated measure to fill. Not able to share the visual due to data privacy. 

Measures:
Total Revenue = CALCULATE(SUM('Table1'[Amount]),'Table1'[GroupName] ="Revenue")
Total Expenses = CALCULATE(SUM('Table1'[Amount]),'Table1'[GroupName] ="Expenses")
Net Profit = [Total Revenue] - [Total Expenses]

Values_Measure = SWITCH(SELECTEDVALUE('Table1'[GroupName]), "Profit Before Tax", [Net Profit], CALCULATE(SUM('Table1'[Amount]))

I would like the resulting Matrix Table to look like this using the Values_Measure Measure in the row