Hello,
I watched the Financial Reporting Template course and have tried to replicate the same for some financial data I have.
I am having challenges with the Switch True formula (below) to populate the defined Sub-totals.
.
I have attached the pbix file.Financial Report Example.pbix (289.8 KB)
Feedback will be greatly appreciated.
Welcome to the forum! I think you’ll find to be a great community and a really helpful resource.
Your SWITCH statement actually looks fine to me.
I think the problem is in your data model, and as is often the case, I think the villain here is the dreaded “autodetect new relationships” option which somehow auto-defined the relationship between your IS Simple Template table and your Organisation Data table as a valid one-to-many, when in fact it currently is a many-to-many. You can see this if you delete the relationship and then try to re-create it manually by dragging the FSL_KK field between the two tables:
The reason it is many-to-many is that you have multiple blanks in this field for the subtotal headers:
So, when the SWITCH statement gets to the subtotal lines it is unable to resolve them.
If you fill in the missing data and re-create your relationships in your data model, I think it will work fine.
- Brian
Hi Kamau,
Hope your well.
I have been looking at your financial template and there are a few points to note:
There is no date table dimension! This is critical if you want to produce valuable financial analysis and make your life easier. There is loads of videos and even a script available, which you can modify for your particular financial year all on Enterprise DNA.
There is no need for a relationship between the template table and the Fact (organisation data) this is what the Switch/True function is doing for you.
As you are, comparing current item to a match the spelling and spacing must be EXACTLY the same as the value you are looking for I created a calculated column with a TRIM in your template table ( normally better do this in the query editor).
Some of your Key measure are evaluating to blank Is this correct???
I have amended you current Target 20 column (02 Summary BUD FY20) and you are now getting the subtotals and line details. The subtotals missing you need to revisit the underlying measures.
Hope this helps!!
Financial Report Example ha.pbix (291.1 KB)
1 Like
Many thanks Brian.
Will fill out the blank spaces then redefine the relationship
Many thanks Haroon.
Yes, some of the measures are blank/null as some data is missing.
Will review the measures for the subtotals.
I would suggest trying @haroonali1000’s solution first. My post assumed your underlying data model was correct and diagnosed how to fix it. His solution proposed a different data model more suitable to the context of the financial reporting approach you are using. Unless for some reason you are tied to maintaining the relationship between the IS and Organisation tables, I would go with his approach.
Either way, you will want to add the date table and turn off the auto-detect relationships option.
- Brian