Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kenthub
Helper I
Helper I

Profit and Loss statement - help with hierarchy

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

 

I'm trying to get this:

Image2.png

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!

 

17 REPLIES 17
kenthub
Helper I
Helper I

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!

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).

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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?

 

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF link to your blog post? 

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)

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@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

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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
Impactful Individual
Impactful Individual

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

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

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

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

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

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
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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/



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.