Forum Discussion
Setting up a mapping table for a nested income statement
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. W...
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
Solution Supplier
Solution Suppliershare the dummy data pbix file for easy reference.
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