Financial reporting and mapping best practices

[DataNinja777]

Hi Power BI community,

I'd like to check with those of you who do financial statements reporting what is their opion regarding the best practice of mapping of GL accounts to financial disclosure lines in specific cases. My impression so far has been that it should always be unique GL accounts to be mapped to specific aggregating financial statements lines, but I've seen cases where mapping of GL to the financial statements are altered depending on whether the balance is positive (assets) or negative (liabilities) such as in case of deferred tax liabilities and deferred tax assets.  In my opion, it appears simpler to just have unique GL for aggregating lines, but this requires reclassificaiton to be made at the time of bookkeeping to different GL or reclassificaiton entries made by using PQ at data transformation of the fact table.  I am not able to create a relationship between the GL mapping and aggregating lines as it is because the relationship is not one to many, but many to many for certain GL because of the situation above.  I consider there are multiple options to deal with this.

1. Keep the data model simple and clean by creating another GL accounts to deal with assets and liabilities cases where different mapping is required for negative and positive balances. ← More real manual work is required.  

2. Write a dax to deal with if the balance of the account is positive, override the existing mapping and use different categorization for certain GL.  

3. Use power query to reclassify negative GL balances to dummy GL which doesn't need to exist in ERP which is mapped as one to many to the disclosure lines.  

I'd like to hear what other accoutants consider as the best practice.  

Thank you!

[v-jtian-msft · Accepted solution]

Hi,@DataNinja777.
I am glad to help you.
 

You have provided multiple options: mapping of GL accounts to financial disclosure lines in specific cases

I think using Dax for dynamic classification is a very good approach: write DAX formulas to dynamically process GL accounts based on their balances, without having to modify the data model. Using this approach reduces human manual operations and keeps the data intact.
Adding tags or attributes to each general ledger account is also a good way to indicate which financial statement line it should be mapped to via a tag. This keeps the mapping simple at the general ledger account level while using the tag or attribute to identify the statement line.

I think you can get some inspiration from the excellent software available on the power BI website.

URL：Business Apps – Microsoft AppSource

There is a wealth of apps, you can download the apps according to your needs, from which you can get  nice solution ideas!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataNinja777]

Thank you very much @v-jtian-msft for your advice. 

I was debating between whether to use dax with conditional formula in the calculated column in the fact table or to use power query to transform the fact table for the relevant GL to bifurcate into #####_A to indicate assets and #####_L to indicate liability and then add those dummy GL in the mapping for GL field instead of ##### to be aggretated into respective disclosure lines without any ambiguity.  I have a feeling doing in this simplified manner will prevent total not summing up issue which can occur when using if formula.  What do you think?  The issue I can foresee which may occur is that with these methods, if the assessment of assets and liabilities has to be made on the total balance over different periods, fixing the mapping in calculate column for each period will not yield the correct overall assessment of whether to map to assets or liabilities over the multiple periods.  My personal preference is to keep the data model and calculation as simple as possible so that reporting can be done with just adding up in line with the mapping without performing acrobatic dax  😊. 

Best regards,