Financial Reporting Sub Totals are Blank

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