Can anyone help me build a table with different totals rows for gross and net

[CHAMBERS]

Hello

I have a table with 3 columns, one for area, one for cost and the last which shows if the amount is income or expenditure.  I want to be able to create a table that shows the area and cost but have 3 different totals rows for Gross expenditure, gross income and net expenditure.  

I also want to be able to filter this table by area and have the totals amounts change.  Everything I have tried so far such as union and add rows gives me the totals rows i want but they do not filter when I only select certain areas

Any help would be much appreciated!!

[Anonymous]

Hi  @CHAMBERS ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yilong Zhou

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

[DataInsights]

@CHAMBERS,

You could create a table like the one below:

Example measures:

Cost =
SUM ( 'Table'[Cost] )

Gross Expenditure =
CALCULATE ( [Cost], 'Table'[Cost Type] = "Expenditure" )

Gross Income =
CALCULATE ( [Cost], 'Table'[Cost Type] = "Income" )

Net Expenditure is the sum of all Cost Type, so you could use the base measure Cost or create a separate measure.

[CHAMBERS]

What I'm after is something that looks like this (imagine there are a list of different areas under business area).  I need the different totals to appear as rows in the same table.  At the moment, this is 3 tables layered on top  of each other which would be fine if the number of business areas stayed the same but i need to be able to filter on business area and have the totals filter too

[DataInsights]

@CHAMBERS,

See if you can adapt the solution below:

https://community.fabric.microsoft.com/t5/Desktop/Building-a-P-amp-L-matrix-with-calculated-lines-da... 

EBITDA would be Net Expenditure in your example. You can specify the position of total rows in the matrix visual.

[DataInsights]

Clarification:

If you make Expenditure amounts negative, then Net Expenditure is the sum of all Cost Type. If all amounts are positive, you could create a measure like this:

Net Expenditure =
[Gross Income] - [Gross Expenditure]