Financial Reporting Sub Totals are Blank

DAX DAX Calculations
1

Hello - I have followed the Financial Reporting tutorial and it worked perfectly for Actuals which come from a SQL server data source. However, I have added forecast/budget data from an Excel data source and the Total Overheads sub total doesn’t show a value, I have watched the tutorial again, I have checked the DAX and the P&L template headings and I can’t work out what is goung wrong, please can someone have a look at my pbix file and the measures in Key measures - Linked Forecasts and see what I have done wrong, pbix and P&L template are attached . Thank you so much Lizzie
Financials with Forecasts Example.pbix (9.4 MB)
Data Source Financial Reporting.xlsx (25.2 KB)

2

Bumping this post for more visibility.

3

Hello @LizzieTompsett ,

Thank You for posting your query onto the Forum.

Upon checking the PBIX file it’s found out that there’s absolutely no problem with the DAX part but there’s a problem with the data model and the way the relationships are created between the tables.

Firstly, as you suggested that you were following the Financial Reporting course. So in Financial Reporting course, nowhere it’s been showcased that we need to create a relationship between the Fact Table and the Template file.

The main reason why it actually worked for the Actuals and not for the Budgets and the Forecasts is because the “Forecasts Combined” table is being joined with the “P&L Template” table and therefore it didn’t worked. See the screenshot provided below -

Relationship Status Between P&L Template and Forecasts Combined

Since based on the relationship created it evaluated the results of all the “Account Numbers” that were there in both the tables. So now, since the sub-total headers don’t have any “Account Number or Nominal Group Numbers” based on which the relationship is created it didn’t evaluated the resuts for them. And therefore, the first thing that you need to do here in order to achieve the results for the sub-totals is to remove the relationship that has been created between the Forecasts table and the Template table.

Important Note: Never create a relationship between the Tables from the model with the Template tables since template template tables are like “Supporting Tables” and nothing else. They’re not Dimension Tables or Lookup Tables.

Hoping you find this useful and helps you in achieving the results that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

2 Likes
4

Hi @Harsh - Was looking into the same. Agreed with your points.

Hi @LizzieTompsett - Try Harsh approach however If removing relationship is not an option now, then can try creating something like below. It is first removing the relationship affect and then returning the values for desired Groups. Will need to add filter for Type if need to further split.

Forecasts Overheads = CALCULATE([Forecasts Value],ALL('P&L Template'),'Forecasts Combined'[Nominal Group] IN {"740", "741", "742", "743", "744", "745", "746", "747", "748", "749", "750", "751", "752", "753", "754"}) * -1

Thanks
Ankit J

5

Hi @LizzieTompsett, did the response provided by @Harsh and @ankit help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

6

Thanks Harsh, I never would have thought about the relationship. Once it was removed the sub totals appeared as you said, thank you so much for pointing me in the right direction, very much appreciated.

7

Thanks Ankit, I really appreciate your input

8

Thanks - harsh solution has been marked as such, I did need to do one small dax tweak as my data table has both forecast and budget values but not a problem after harsh told me about the erroneous relationship :slight_smile:

9

Hello @LizzieTompsett ,

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh

1 Like