Setting up a mapping table for a nested income statement

[Anonymous]

Hi,

It's been some time since I have used dax/power query to do this so I am hoping it has gotten easier.

I have an income statement layout where I want the layout in Power BI to look like this.  What is the best way to map each GL so that I can have the subtotals and totals calculate correctly?   I will probably need to add IS line iems later so I am hoping to come up with something fairly dynamic. Thanks

Sales	100
Sales-Direct	100
Sales-Subcontractor	100
Sales - Labor	100
Sales - Freight	100
Total Sales	500
Sales Returns & Allowances	-100
Net Sales	400
Cost of Goods Sold	50
Cos-Direct	50
Cos-Subcontractor	50
Cost of Goods Sold - Labor	50
Cos-Freight	50
Sales COGS	250
Sales Contribution	150
% Sales	38%
Loss/(Gain) on Inventory	12
Vendor Rebates	44
Early Pay Discounts	58
Freight Variance	62
Transfer Freight	38
Total Cost of Goods Sold	464
Gross Profit	-64
Gross Profit %	-16%

[Anonymous]

Hi @Anonymous ,

It's hard to see the correlation between them from the sample data you provided. Can you tell me the calculation logic for these two columns in the expected output?

Best regards,

Mengmeng Li

[Anonymous]

I know that. I am trying to get the totals and subtotals to work and am wondering the best strategy for mapping the data.

[elitesmitpatel]

share the dummy data pbix file for easy reference.

[Anonymous]

Thanks. I am trying to create something that looks like this using the data below in bold. I need this to work in Power BI or in Excel as the client really wants to use an Excel UI.  I just need help with how to create the mapping such that I can do subtotals, insert blank rows, etc. etc.   Thanks

PS. I cannot upload a file. 

IS Item	Original Index	Total
Sales	1	400
Sales-Direct	2	100
Sales-Subcontractor	3	50
Sales - Labor	4	20
Sales - Freight	5	0
Total Sales	6	0
Sales Returns & Allowances	7	-25
Net Sales	8	545
	9
Cost of Goods Sold	10	20
Cos-Direct	11	21
Cos-Subcontractor	12	22
Cost of Goods Sold - Labor	13	23
Cos-Freight	14	24
Sales COGS	15	110
Sales Contribution	16	435
% Sales	17	20%
	18
Loss/(Gain) on Inventory	19	30
Vendor Rebates	20	32
Early Pay Discounts	21	33
Freight Variance	22	34
Transfer Freight	23	35
Total Cost of Goods Sold	24	599
	25
Gross Profit	26	-54
Gross Profit %	27	-10.0926

IS Item	Amount	Gl
Sales	100	49
Sales	100	44
Sales	100	45
Sales	100	46
Sales-Direct	50	47
Sales-Direct	50	48
Sales-Subcontractor	25	43
Sales-Subcontractor	25	42
Sales - Labor	10	41
Sales - Labor	10	41
Sales Returns & Allowances	-25	40
Cost of Goods Sold	20	50
Cos-Direct	21	51
Cos-Subcontractor	22	52
Cost of Goods Sold - Labor	23	53
Cos-Freight	24	54
Loss/(Gain) on Inventory	30	55
Vendor Rebates	32	56
Early Pay Discounts	33	57
Freight Variance	34	58
Transfer Freight	35	59

[elitesmitpatel]

please tag other people for help

[elitesmitpatel]

Q-> What is the best way to map each GL so that I can have the subtotals and totals calculate correctly? 
Ans-> Mapping is compeltely depended on your company requirement and the expected output. We have less data to make this decision.

If i helps Kudos to work and Accpet it as Solution.