Calculation Problem

[hirendvashi]

I have two tables as explained below.


1. Accounting Year table: contains one row for each accounting year and the budget amount. There are multiple accounting years.

Accounting Year     Budget Amount
2020-21                    $ 200,000.00
2021-22                    $ 250,000.00

2. Expenses table: contains multiple rows for expenses throughout the accounting years.
Accounting Year     Invoice Date    Invoice Amount
2020-21                     10/1/2020        $ 30,000.00
2021-22                     7/1/2021          $ 50,000.00
2021-22                     8/10/2021        $ 60,000.00


I want to create a dashboard for Accounting Year 2021-22 that shows Accounting Year, Budget Amount, Total Invoice Amount, and Remaining Budget.
Accounting Year     Budget Amount      Amount Invoiced      Remaining Budget
2021-22                    $ 250,000.00            $ 110,000.00              $ 140,000.00

Calculation field content: Accounting_Year_table.Budget_Amount - Expenses_table.Invoice_Amount

When I create a calculated field (as shown above) for the 'Remaining Budget' that subtracts the total of 'Invoice Amount' from the 'Budget Amount', and try to show it as a summary visualization on the dashboard, it does not give me the correct remaining budget amount. It shows me more amount. What mistake am I making? What is the correct way to get the Remaining Budget amount?

[bus_pass_man]

Yeah, even with column dependency set an expression which is referencing measures from different fact grains will produce invalid results.


Create a view of your expense table. Exclude from it those columns which you want summarized. In your case you would exclude invoice date.  I'm assuming that there are other columns.  You will need to make a decision about each of them.

In the advanced properties set the usage of the view to be summary.

This will pre-summarize the values of your expenses table and you will have one record for each unique set of non-measure column values in the view.

Assuming that you just have accounting year and invoice amount in your view, you should see something like this, although as I mentioned earlier you probably will have additional dimension keys to think about.

Accounting Year        Invoice Amount
2020-21                  $ 30,000.00
2021-22                  $ 110,000.00


Use this in your expression. Because the values have been summarized to the same grain as your budget, you should get the results you are seeking.