cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
josemalonso
Regular Visitor

cumulative/running totals per account and category in different tables

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.

1 ACCEPTED SOLUTION
jdbuchanan71
Super User II
Super User II

@josemalonso 

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

 

View solution in original post

5 REPLIES 5
daxer-almighty
Solution Sage
Solution Sage

@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
Continued Contributor
Continued Contributor

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.

 

 

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 total1. 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:

Scenario 2. without Date Column.png

 

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:

Scenario 3. with Account No from CoA.png

 

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:

Scenario 4. Desired outcome but wrong figures.png

 

I hope this helps to explain the issue further.

 

jdbuchanan71
Super User II
Super User II

@josemalonso 

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

 

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors