Profit and Loss statement - help with hierarchy

[kenthub]

I know its been asked and I have checked all the suggested articles, but I need some help!

I'm trying to get this:

This image is from thebiaccountant. 

The guy talks way over my head and every time I feel like I have a solution, it doesn't work or the DAX formula doesn't do what I want it to.

I'm trying to sort the GLAcountName into the appropriate Sales, Cost of Sales, and Expense categories but I am struggling here. Any tips or suggestions would be great.

I've tried new columns, and tables but really don't know how to get the data correct. 

I've uploaded the pbix below so you can see what I am working with. Well I can't even seem to find where to upload that!

[kenthub]

Here is the PBIX File

We do utilize a good Chart of Accounts. Take a look and let me know what you think....

If we need to make the changes from the report we pull from NAV then that might have to be the option. 

Thanks all!

[ImkeF]

To help you set up my model, you would have to provide that Chart of accounts table as well.

Or to be more precise: We need a table which contains the definition of which accounts go into which (sub-) totals (or account groups).

[kenthub]

Gotcha. How or what would you use to connect the two tables? Like the YouTube video above shows there are headings, subheads, sub-subheadings, etc.   Would it be best to associate a number linking the specific Chart of Account (Revenue, Cost of Sales, etc.) to the desired GL number? Or would you used some other method of connecting the tables?

[ImkeF]

The connections between the tables can be seen in the sample files from my blogpost.

If you're struggling to apply it to your data, I would need to see your tables.

[kenthub]

@ImkeF link to your blog post? 

[ImkeF]

It's in your first post - I'm "The guy" 😉

(Unfortunately Microsoft decided to delete the signatures here in the forum and I gave up adding them manually)

[kenthub]

@ImkeF and @MattAllington

Ok so I created a chart of accounts based upon the pdf version that we send out. I am having trouble however with the sort. I have tried sorting on Category Sales and GLAccountName using both of my SortOrder and CategorySortOrder. However neither works and populates error messages. If I can get the sort order correct then I am finished! for now 😄

Any advice? Thanks!

New File HERE

[ImkeF]

It seem that you haven't used any of the both models Matt and I have referenced here.

Don't see how can help you on that basis unfortunately.

[kenthub]

For my limited knowledge yours was amzaing to look at but I had no idea what was doing what! I just made a simplified one. I'll dig further for my sort order problems. Thanks for your time however!

[austinsense]

Here's what we use in our Power BI for Accountants class. this is the "cascading subtotals" approach ... https://www.dropbox.com/s/pd5blba2c53kni8/Financial%20Reporting%20-%20Full%20Package.pbix?dl=0

[newroy2015]

Hi Austin, would you mind sharing your example work again? I just clicked into the link being shared and the content was deleted.

We are now desparately looking for solution to bring the total of each category from top to bottom, exactly like what the normal P&L Statement show instead of what the Power BI can offer.

It would be great if I can learn from you and your work.

Many thanks!

Roy

[karaoan]

In this post I am covering a reasonably simple approach for PL statements with example: https://www.managility.co/profit-loss-dynamic-sub-totals-in-power-bi-excel-dax/

Martin

Write-Back, Planning & Financial Models with Power BI -> https://www.acterys.com

[newroy2015]

Hi Austin, would you mind sharing your example work again? I just clicked into the link being shared and the content was deleted.

We are now desparately looking for solution to bring the total of each category from top to bottom, exactly like what the normal P&L Statement show instead of what the Power BI can offer.

It would be great if I can learn from you and your work.

Many thanks!

Roy

[kenthub]

Great! I didn't see the downloadable files. I'll sift through them to see what you have done and get back to you. 

Thanks!

[ImkeF]

Hi @kenthub,

you cannot upload files directly on the forum here. Instead, load them to a web storage (like dropbox ie) and paste the link here.

The link @MattAllington has pasted is a completely different concept compared to mine and will not help you for my model. You have to choose between the 2.

[MattAllington]

The most important thing to make a P&L work is having a good chart of accounts as a lookup table. I learnt everything I know from this video. https://www.youtube.com/watch?v=ojHZkWkEY7Q/

[Anonymous]

O dear... I was about 80 pct finished in building a p and l, however I think these links you two provide may bring me back to the drawing board.