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. 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% |
6 Replies
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.share 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
