Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
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
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!
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
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!
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/
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
84 | |
66 | |
49 |
User | Count |
---|---|
140 | |
114 | |
108 | |
64 | |
60 |