cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## Totalling Ledger NAV

I think the title is not clear at all, but it is difficult to give my problem 'a name'.

I have the following table (an example, of course there is more data in each row)

GL Account Number - Account type - Totalling

10 - Account - 'empty'

15  - Account - 'empty'

20 - Account - 'empty'

25 - Total - 10..25

The problem is with the bold rows of which the account type = Total. For this rows the Totalling column is filled with the Gl account numbers that have to be summed up in order to get a Total balance. In NAV the '..' symbol means that it sums up the balance amount for the first number until and with the last number.

Now I want to filter on the data in the rows, based on the GL account numbers with the "Total" Type. So if i select 25, i get the amount of all the GL account numbers between and with 10 to 25.

How can i get this fixed? I already splitted the column in 10 and 25 but i don't know how to go further. Anyone who can help?

The total rows also have a name like Administration costs or something, it is also good if i can fill every normal (GL account type = Account) with this name, so i can filter on that but i also can not get that to work.

1 ACCEPTED SOLUTION
Employee

@Guustc

According to your example, we need to update the formula of Kostensoort column as below.

```Kostensoort =
VAR Name_Index =
IF (
ISBLANK ( Grootboekrekening[Naam] ),
CALCULATE (
MIN ( Grootboekrekening[Index] ),
FILTER (
Grootboekrekening,
Grootboekrekening[Index] > EARLIER ( Grootboekrekening[Index] )
&& Grootboekrekening[Naam] <> BLANK ()
)
),
Grootboekrekening[Index]
)
RETURN
(
LOOKUPVALUE ( Grootboekrekening[Naam], Grootboekrekening[Index], Name_Index )
)```

Regards,

9 REPLIES 9
Helper III

For a Power Query based solution to this problem - read this post:
Dynamics NAV account schedule

Anonymous
Not applicable

I had the same challenge and I think I solved it using a measure that dynamically calculates the sum without using calculated columns and it goes 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]) ))
)
)

Meaning it goes for every GL account, checks whether it is a Totaling account or not, and it performs the sum of all accounts that the totalling includes (10..25 - 10,11,12,...23, 24, 25), and for a 'simple' account it computes the sum of just that account from the GL Entry tables.

It works for me and rerturns the right numbers. Let me know what you think.

Frequent Visitor

Can you create a measure that sums up the Amount field of 5 selected accounts in on the G/L Entry table?

Thanks.

Frequent Visitor

Can you write this DAX Language if I just want the Total Amount for 5 selected GL Accounts e.g. 111001, 293028, 283746, etc

Thanks.

Employee

@Guustc

As I understand it, you want to get a total amount from the first row to current row within same group if current row Account Type is “Total”. Right?

You can create another table, includes the “GL Account Number” whose type is “Total”. And then create a calculated column to sum up them with following steps:

1. Add an index column for the table in Query Editor.
2. Create a new table for the slicer.
```TotalTable =
SUMMARIZECOLUMNS (
Table1[GL Account Number],
FILTER ( Table1, Table1[Account type] = "Total" )
)
```
3. Create a column for the original table.
```Total_Amount_Column =
VAR TypeIndex =
LOOKUPVALUE ( Table1[Account type], Table1[Index], Table1[Index] - 1 )
RETURN
(
IF (
Table1[Account type] = "Total",
CALCULATE (
SUM ( Table1[Balance amount] ),
FILTER ( ALL ( Table1 ), Table1[Account type] = TypeIndex )
)
+ Table1[Balance amount],
BLANK ()
)
)
```
4. Drag a slicer and a table into your canvas.

For another method you mentioned at last, if the total rows also have a name like Administration costs or something as below. We can create a new column with following formula to fill every normal (GL account type = Account) with this name. (The index column is still required here)

```Name_Column =
IF (
ISBLANK ( Table1[Name] ),
LOOKUPVALUE (
Table1[Name],
Table1[Index], CALCULATE ( MAX ( Table1[Index] ), ALLEXCEPT ( Table1, Table1[Account type] ) )
+ 1
),
Table1[Name]
)
```

Regards,

Helper I

I wanted to implement the second solution, but I can not get it to work. I made an index colum and used the formula you gave me, but it does exactly the opposite. I tried to change the if_false value to if_true, but then i get an DAX error.

You can see the example above.

I tried many things, but i do not know what i am doing from. Do you have an idea?

Employee

@Guustc

According to your example, we need to update the formula of Kostensoort column as below.

```Kostensoort =
VAR Name_Index =
IF (
ISBLANK ( Grootboekrekening[Naam] ),
CALCULATE (
MIN ( Grootboekrekening[Index] ),
FILTER (
Grootboekrekening,
Grootboekrekening[Index] > EARLIER ( Grootboekrekening[Index] )
&& Grootboekrekening[Naam] <> BLANK ()
)
),
Grootboekrekening[Index]
)
RETURN
(
LOOKUPVALUE ( Grootboekrekening[Naam], Grootboekrekening[Index], Name_Index )
)```

Regards,

Helper I

@v-sihou-msft that works fine! Enormous thanks! Almost got a headache out of this problem, this really helps me go forward 🙂

Helper I

Anyone who can help?

Maybe ideas to transform the data?

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors