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
THEG72
Helper V
Helper V

Design Advice: Accounting Consolidations Best Practices

HI @ImkeF

 

I am building a model to consoldate 4 Supplier Ledgers and was wondering if you had previously blogged about this process on your biaccountant website?

 

I am aware that creating bi directional relationships maybe overcomplicating model and or making model run less efficiently... Is there new methods to handle these kinds of requirements...maybe like using TREATAS or some other virtual relationship options?

 

I was looking for the best approach to:

 

  • Consolidate Tables or the same structure together for easier reporting
  • Grouping tables so single measures can output outstanding balances ie. Report by Supplier Name, Business Name.
  • Break up each invoice by PurchaseTypeID 
  • The Top Level Key is the Company Files Table holding each data files specifics.
  • The MergesSuppliers Table is the result of combining all the SupplierCard Tables (1 to 3). I have done this to avoid having to expose end user to each of the SupplierCard Tables 1-3 to obtain each supplier "Name" field from each compan file table. I can then hide each of the sub tables 1 to 3 and just leave the merged version

Supplier Consolidation ModelSupplier Consolidation Model

Has anyone tried consolidations in Power BI previously? I am looking to provide the end user the least amount of tables data to look through..



2 ACCEPTED SOLUTIONS
ImkeF
Super User
Super User

Hi @THEG72

No, I haven't blogged about it yet.

But when I do consolidations, I use only 1 table and add a column to it for the different company names (same goes for the combined keys with the dimension tables).

As long as you leave the signs from the bookkeeping figures untouched, the figures fall automatically into place then.

Might be worth a blogpost actually 😉

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

View solution in original post

Anonymous
Not applicable

Is the table structure of your 3 Supplier ledgers tables the same?  Is the table structure of your 3 Supplier Card tables the same?

 

I would personally turn 'Enable Load' off on your Ledger and Supplier card tables create  new tables using:

Source = Table.Combine({SupplierLedger1, SupplierLedger2, SupplierLedger3})
Source = Table.Combine({SupplierCard1, SupplierCard2, SupplierCard3})

If you need to be able to filter back to the original tables, create a new column in Power Query for each table in its own code that simply holds a text string you can filter on.  You could therefore remove the MergedSuppliers table.

 

From here, you should be able to do most of what you are talking about using measures.

 

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

Is the table structure of your 3 Supplier ledgers tables the same?  Is the table structure of your 3 Supplier Card tables the same?

 

I would personally turn 'Enable Load' off on your Ledger and Supplier card tables create  new tables using:

Source = Table.Combine({SupplierLedger1, SupplierLedger2, SupplierLedger3})
Source = Table.Combine({SupplierCard1, SupplierCard2, SupplierCard3})

If you need to be able to filter back to the original tables, create a new column in Power Query for each table in its own code that simply holds a text string you can filter on.  You could therefore remove the MergedSuppliers table.

 

From here, you should be able to do most of what you are talking about using measures.

 

Hi @Anonymous @ImkeF,

 

Thanks for your replies. The table structure is EXACTLY the same for all ledgers... its from an accounting database by just differnet connections to each business file.

 

I was looking to see whether it was better for efficiency to combine the Supplier Card Table and Ledger data first and then do the measures or create measures for each table group and then sum the measures for group totalling?

 

I also need other dimensions like PurchaseType (Item or Service), OrderStatus (open, closed, quote) which are the same accross all fact tables.

 

When a new supplier is added to the live supplier table 1and a refresh is scheduled....will your Table Combine work if you dont enable Load on the SupplierLedger1 source?

Anonymous
Not applicable

Enable Load is a very confusingly worded option.  The table does refresh and accept new data but isn't loaded into the data model for use in your reports.

 

I also noticed in your reply you mention you need other FACT tables etc.  In my experience I've found this to be worse in Power BI than simply loading in the tables you need and relying on measures to do the heavy lifting.  The Vertipaq engine that Power-BI is build on is fantastic for data and using FACT tables like you would in SQL just bloats and slows down your model from my experience.

 

When i picked up my current role, the early produced PBIX files were full of staging tables and fact tables.  After stripping them out and optimising the data held, i often shrunk the PBIX files by around 20% and sped up complex queries.

Hey @Anonymous.

 

I have also found that publishing the base model and core measures only on Power BI service and then building the report using the model from the PBI service a great way to optimise and speed up things for these types of requirements.

 

Your definately right about the need to strip down the tables the Ledger tables fields need to be cut back a bit and reduce its width.

 

The Supplier Ledger is the summary ledger. There are like supporting ledgers with other detailed information for example an item ledger as stock items and service ledger. These all just represent different types of purchases.

 

At this point I was just working on the balances and not the detail as such. 

 

Okay thanks for clearing that up...It should state Disable Report Load but Upfresh data still.. 🙂 Cheers!

 

 

 

 

 

Anonymous
Not applicable

I should clarify that i'm not against using FACT tables in your views to reduce the heavy load of refreshing.  I'm simply against having Power Query and Power BI create FACT as part of the import.

 

FACT tables from your views is a great way to bring in only a small subset of data instead of downloading a whole table.

So @Anonymous

 

Using the Table.Combine gives me this view with the 5 ledgers combined.

 

The last two tables on the right show the combine Supplier Card and Ledger (Fact) data.

 

I was just thinking that i would need to cater for duplicate names in each card per entity?

 

Table.combine for 5 Fact SourcesTable.combine for 5 Fact Sources

I created a CompanySupplierKey on the SupplierCard dimension table as Supplier Cards can be duplicated accross different entity files..

Added new Index for cards form each entityAdded new Index for cards form each entity

 

....do i need to the same on the SupplierLedger Fact tables

Anonymous
Not applicable

After you have done the combine, you could simply do a "remove duplicates" based on a unique column.  This would then give you a concise list of suppliers, if that suits what you are attempting to do.  It makes sense to me.

 

As for your Supplier Ledger Fact table, if you have any unique identifers you are creating then yes you'll need them in all tables you want to link using that value.

 

 

Thanks Ross I will give that a go cheers for your advice again!

 

ImkeF
Super User
Super User

Hi @THEG72

No, I haven't blogged about it yet.

But when I do consolidations, I use only 1 table and add a column to it for the different company names (same goes for the combined keys with the dimension tables).

As long as you leave the signs from the bookkeeping figures untouched, the figures fall automatically into place then.

Might be worth a blogpost actually 😉

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

Hi @ImkeF

 

When you say you use only 1 table...does that mean you are consolidating the Supplier Cards into one table and the Supplier Ledgers into one table and then writing the consolidated measures from there...as opposed to writing measures for each ledger grouping...So using a combine table step first


Are you still enabling the reload on the orginal source ledgers for each entity?

 

 

No need to have them at all:

Ideally, you can import from folder and just expand your binaries there into one (consolidated) table.

But if your source-files aren't in one folder, you can simulate this behaviour by putting all adresses to the files into one column (one row per file) and apply a function to open them in a new column. (

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

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.