Totaling Financial Accounts in Power BI

For BI reporting the financial general ledger in (i.e.) Dynamics NAV you have the G_L Entry table as a fact table and GL Account which is a dimension table linked.

No problem with this.

However … there is always a however…

However, there are the so called totalling accounts, which as the name suggests are accounts that total (sum) multiple other accounts.

These accounts in the source table of Dynamics NAV – Navision, have filled the column ‘Totaling’ with a range value like 0015 – 0999, or in the picture below.

What you can do in Power BI to solve the calculation of the Totaling accounts is first, as suggested in the picture, to split the value by ‘..’

As to get the minimum account number and the maximum account number to hopefully later use in a dynamic DAX formula.

Then hopefully finishing with the ETL part you go and start writing the DAX formula.

We would somehow need to write a DAX formula that includes two cases.

The first case is the easy one, that make a sum of the values in the G_L entry table for ‘normal’ account.

The other case would be to calculate the computed total for the ‘totaling’ accounts.

And for the totaling in theory we would just need to go through the GL Accounts table and include the whole range of accounts that are defined by the totalling column.

And we will do just that.

The formula looks like this:

GL amount =
SUMX (
‘GL Account’;
    IF (
        NOT ISBLANK ( ‘GL Account'[Totaling Max] );
        CALCULATE (
            SUM ( ‘General Ledger'[Amount] );
            FILTER (
                ALL ( ‘GL Account’ );
‘GL Account'[Account Code] >= EARLIER ( ‘GL Account'[Totaling Min] )
&& ‘GL Account'[Account Code] <= EARLIER ( ‘GL Account'[Totaling Max] )
            )
        );
        CALCULATE ( SUM ( ‘General Ledger'[Amount] ) )
    )
)

Explaining the formula goes like the following:

1. For every account (we open a row context on the GL Account table)
2. We check the case, simply by checking if we have a value in the Totaling Max column (if there was a range, it must have been splitted)
3. Case 1 – totaling account – sum the amount of accounts of the whole range, meaning bigger or equal than the Totaling Min account, and smaller or equal than the Totaling Max account (we open another row context on the same table GL Account and reference the Totaling Min/Max columns by using the EARLIER function)
4. Case 2 – simple account – sum of the amount

And this only DAX measure would do for this type of totaling accounts to work.

Not using Calculated columns, but just a dynamic measure.

Why not to overuse calculated columns I will briefly cite another Power BI expert:

The number 1 mistake I see in self taught DAX students with an Excel background is the use and over use of Calculated Columns instead of Measures.

You let me know what do you think of this in the comments below.

If you need any kind of help similar problems or writing DAX formulas, you can contact me.