Forum Discussion

ThiemenSiemensm's avatar
ThiemenSiemensm
Frequent Visitor
3 years ago
Solved

subtotals

Good afternoon,

 

I have a problem using an addition scheme to calculate (sub)-totals where the values to be filled in the scheme coming from a different table (NAV).

I have two tables, 1 is the scheme, called "Rekeningsschema", the other is the data called "Grootboek_posten_overzicht".

 

In the matrix below I displayed the scheme with corresponding values from data table (linked via the 'No' column). However, calculating the totals does not work, I want to do something like shown in the picture below. In words it could be explained as calculating the total amount of the data table over a specific range of 'No' values from the scheme, to be filled in a 'End-total' No in the scheme. Does someone have a solution to this? Manually filling the min and max in DAX does work! I need it parametric. Identation via the begin-total, end-total column would also work, but I don't know how.

 
Fault in measure DAX

 

Working manual DAX expression.

 

 

The Matrix

 

 

Need Help
  • TomasAndersson's avatar
    TomasAndersson
    3 years ago

    Hi!
    Totaling from NAV? I wrestled with that a while back. This is problably not the most efficient solution, but I managed to get it to work.

    First I added an Index column to the Chart of Accounts (in Power Query) so that I had that alongside account no, First Min and First Max. I also had the NAV totaling description as a columns, sort of like this:

     



    Then i used the following:

     

    Totaling Account SEK = 
SUMX('Chart of Accounts', 
    IF( 
       'Chart of Accounts'[Totaling]<>"",  // If not a totaling account 
        CALCULATE ([Sum SEK], //Use basic sum of amount from general ledger table  
            FILTER(ALL('Chart of Accounts'), //else calculate sum of all accounts between min and max
            'Chart of Accounts'[Index] >= EARLIER('Chart of Accounts'[Totaling 1 Start]) 
            && 'Chart of Accounts'[Index] <= EARLIER('Chart of Accounts'[Totaling 1 End])
        )
    )
)

     

     You'd have to change the column names, and instead of 'Chart of Accounts'[Totaling]<>"" you'd have to look for a blank First Min or First Max instead.

     

    This is a measure as well, and maybe needs to be adjusted if you want a calculated column.

    Hope this helps you a bit. I don't have time to write a better guide right now but let me know otherwise and I can look into it in a day or two.

     

    Good luck! 

3 Replies