Financial P&L - How to Layout Data

[pedanticpad]

Hi,

I have system driven data laid out like the below;

Month	Salesman	Invoice	Sales	Direct Consumables	Direct Payroll	etc
Jan	X	12345
Jan	Y	12346
Jan	Z	12347
Jan	AA	12348
Jan	AB	12349
Feb
Mar

I may be wrong but to create a P&L matrix table I need the data to be like this;

Category	Jan	Feb	Mar	Apr
Sales
Direct Consumables
Direct Payroll
Contribution
Other Costs
Other Payroll
Contribution
Overheads & Non Direct Payroll
EBITDA

Is there any way to achieve this in power BI?  I have over 5,000 lines each month so I need something with minimal manual intervention.

Thanks!

[PaulDBrown]

@pedanticpad 

Check out this demo from Enterprise DNA

Financial Reporting Showcase

And a "how to" video on PnL satements, also from Enterprise DNA:
Creating PnL Statement 

[SivaMani · Accepted solution]

@pedanticpad,

Yes, It is possible in Power BI. Please do follow the below steps,

•  Go to Transform Data (aka Query Editor)
• Select the categories which are plotted as columns like below,

• Click the Unpivot Columns option (under Transform tab) --Highlighted in the above image.

• Select Month Column and click the Pivot Column option (under Transform Tab). A dialogue window will appear to choose the value field.

• Choose your value field and aggregation. Click Ok

Here you go!

Let me know if you have any issues.

Appreciate your kudos!

Regards,

Siva Mani

[pedanticpad]

Thanks so much!

[SivaMani]

Glad that it helped you!

[pedanticpad]

Hi,

Is to possible to do something similar to a table that has been created in PowerBI rather than imported as a query?  I've had to import a table, add several calculated columns, etc to get my desired values but I need to get it laid out like the above to create a P&L.

Thanks,
Paddy