Help needed to create new tables using existing tables and measures

[artfulmunkeey]

Hi, 

I have been struggling with this for some time, and whilst I have been scouring the internet for solutions, I am not sure if I am phrasing the problem correctly in my searches, and would be very grateful if someone could offer some assistance.

I am attempting to build a report on my P&L data. It comes in the form of a report from our ERP resembling the following:

Department	Amount	Currency	Period	Reporting Code
CS		EUR	202001	INC-LAB
				INC-SUB
				INC-OTH
				COS-SAL
				COS-BEN
				COS-BIL
				COS-NON
				SUP-IT
				SUP-MAR
				SUP-OFF
				SUP-INT

I then have a master reporting code table

Title 1	Title 2	Reporting Code
Revenue	Income Labour	INC-LAB
Revenue	Income Subco	INC-SUB
Revenue	Other Income	INC-OTH
Direct Costs	Salaries	COS-SAL
Direct Costs	Benefits	COS-BEN
Direct Costs	Billable	COS-BIL
Direct Costs	Non-billable	COS-NON
Support	IT	SUP-IT
Support	Marketing	SUP-MAR
Support	Office Rent	SUP-OFF
Support	Interest & charges	SUP-INT

a master department table

Department	Department (T)
CS	Corporate Support

and of course a good date table...

I am able to create a matrix which reports on all the values in the Title columns, something like the following (filterable by date and department):

		Current Month	YTD
Revenue		TOTAL €	TOTAL €
	Income Labour	€	€
	Income Subco	€	€
	Other Income	€	€
Direct Costs		TOTAL €	TOTAL €
	Salaries	€	€
	Benefits	€	€
	Billable	€	€
	Non-billable	€	€
Support		TOTAL €	TOTAL €
	IT	€	€
	Marketing	€	€
	Office Rent	€	€
	Interest & charges	€	€

In order to calculate Gross Margin and Gross Margin as % of Revenue, I have created measures, however, I would like these measures to appear in my matrix (I currently can only display them in a separate visual). Ideally I want something like the following:

Title 1	Title 2	Amount	Period	Department
Revenue	Income Labour
Revenue	Income Subco
Revenue	Other Income
Direct Costs	Salaries
Direct Costs	Benefits
Direct Costs	Billable
Direct Costs	Non-billable
Gross Margin	Gross Margin	Measures
Gross Margin	Gross Margin % on Revenue
Support	IT
Support	Marketing
Support	Office Rent
Support	Interest & charges

I suspect I need to create a new table, merging the data from the reporiting with the measures, and adding new rows in the tile 1 and title 2 fields to be able to map it and display it correctly in a matrix.

Is this possible? If so, how? Can someone please help, or point me in the direction of some online material which would guide me through it?

Thanks in advance!

[MattAllington]

Great. I'm glad it helped. 

[MattAllington · Accepted solution]

Take a look at my article here. 
https://exceleratorbi.com.au/build-a-pl-with-power-bi/

and here

https://exceleratorbi.com.au/building-a-matrix-with-asymmetrical-columns-and-rows-in-power-bi/

i have a follow up to the first article. I may release that new to week, so keep an eye out. 

[artfulmunkeey]

Matt, thank you very muhc for this resource, it was very detailed and extremely useful! I spent some time over the weekend and based on your instructions have succefully implemented my P&L!

Thank you very much for your support!