cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
THEG72 Member
Member

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

DesignAdvice2.PNGSupplier 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

Accepted Solutions
Super User
Super User

Re: Design Advice: Accounting Consolidations Best Practices

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 Smiley Wink

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: Design Advice: Accounting Consolidations Best Practices

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.

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


11 REPLIES 11
Super User
Super User

Re: Design Advice: Accounting Consolidations Best Practices

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 Smiley Wink

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: Design Advice: Accounting Consolidations Best Practices

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.

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


THEG72 Member
Member

Re: Design Advice: Accounting Consolidations Best Practices

Hi @Ross73312 @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?

THEG72 Member
Member

Re: Design Advice: Accounting Consolidations Best Practices

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?

 

 

Highlighted
Super User
Super User

Re: Design Advice: Accounting Consolidations Best Practices

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.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Super User
Super User

Re: Design Advice: Accounting Consolidations Best Practices

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.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


THEG72 Member
Member

Re: Design Advice: Accounting Consolidations Best Practices

Hey @Ross73312.

 

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.. Smiley Happy Cheers!

 

 

 

 

 

Super User
Super User

Re: Design Advice: Accounting Consolidations Best Practices

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




THEG72 Member
Member

Re: Design Advice: Accounting Consolidations Best Practices

So @Ross73312

 

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?

 

DesignAdvice3.PNGTable.combine for 5 Fact Sources

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

DesignAdvice4.PNGAdded new Index for cards form each entity

 

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