topic Re: Can anyone help me build a table with different totals rows for gross and net in DAX Commands and Tips

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

CHAMBERS — Tue, 15 Oct 2024 13:33:40 GMT

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!!

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

DataInsights — Tue, 15 Oct 2024 14:11:08 GMT

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.

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

DataInsights — Tue, 15 Oct 2024 14:22:17 GMT

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]

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

CHAMBERS — Tue, 15 Oct 2024 14:35:54 GMT

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

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

DataInsights — Tue, 15 Oct 2024 16:08:28 GMT

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-data-source-is/m-p/4214681#M1324489

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

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

Anonymous — Wed, 30 Oct 2024 02:06:49 GMT

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.