cumulative/running totals per account and category in different tables

[josemalonso]

Hi all,

Newbie hear and suck trying to find my way.

Scenario: Business Central connnected to Power BI to build a financial dashboard.

Main tables I'm using now: General Ledger Entries (GL_Entries) and Chart of Accounts (CoA). There is a 1-n relationship between both on the Account Number.

The CoA has two columns with Category and Subcategory. For example, an account such as 1020 is a bank account whose Category = Asset and Subcategory = Cash.

I need to show charts with cumulative/running totals per Category, Subcategory and Account number. And I should be able to slice those by any of those and also by Date.

I've tried some of the solutions in other posts and got so far to this measure:

Running Total = 

CALCULATE (SUM('GL_Entries'[Amount],

FILTER (

ALLEXCEPT ('GL_Entries', 'GL_Entries'[Account_No], 'GL_Entries'[Date] <= MAX ('GL_Entries'[Date])

)

)

This works at the account level number. If I add this plus the Account_No and Amount to a table visual, it works as expected, and the Running Total shows the right amount. So far, so good:

Account_No.  Running Total

1010            500,000

1020          1,000,000

Now I want to see the total Cash Subcategory Running Total, so I add Subcategory and remove Account_No. Running Total doesn't work well anymore and shows weird figures:

Subcategory  Running Total

Cash             750,000

If I keep Subcategory and add back Account_No, I get the right figures back again on a per line basis such as in:

Subcategory  Account_No.  Running Total

Cash          1010            500,000

Cash          1020          1,000,000

I'm stuck here and I would still need to group once more for Categories, the slice by date and otehr columns...

I'm frankly not even sure if the best approach to this is DAX, or I would be better off preparing the data differently via Power Query...

Any help would be greatly appreciated.

Thanks in advance.

[daxer-almighty]

@josemalonso 

I'm not sure what the problem is (no time to analyse) but you have to be careful which table you're taking columns from to display in your visuals. Do you have a correct star-schema model where you only slice by dimensions and have hidden the fact table's columns? If this is not the case, then this might be the source of your problems.

Please read this just in case: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

Also, I can see that in your formula you're using the column of dates from your fact table. THIS IS A BIG NO-NO. Don't ever do this unless you want to spend the rest of your life debugging your code. Please create a proper star-schema model (read the article above) and your life will be easier. You'll have a much better life. Trust me.

[sayaliredij]

The formula you are using correctly.

I am not sure exactly what do you expect out of Running Total

Generally running total always comes with a date. So always try to Put months in rows followed by account, subaccount, or cash as you want, then running total would make sense

Thanks,

Sayali

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

[josemalonso]

Hi @sayaliredij 

I'm attaching four screenshots to further illustrate my issue.

This is a table with 5 columns, including a date one. Running Totals per Account_No are accurate. Grand Total at the end is not though:

1. All columns, accurate subtotals but not the total

I then removed the Account_No column from the table and now the Running Total is wrong:

I changed Account_No from GL Entries table by No. from the Chart of Accounts table (this is the field where both tables are joined) to test it; this resulted in different but wrong figures again:

This would be my desired result but the Running Total is wrong; it should be the sum of all in the first screenshot, i.e. 1,142,004,69:

I hope this helps to explain the issue further.

[jdbuchanan71 · Accepted solution]

@josemalonso 

Take a look at this article which shows the solution for what you are trying to accomplish.
https://www.daxpatterns.com/cumulative-total/

[josemalonso]

Yes, this put me in the right direction. Thanks.