subtotals

[ThiemenSiemensm]

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

[TomasAndersson · Accepted solution]

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! 

[ThiemenSiemensm]

Thanks, your idea helpt me form the solution. I had to adapt it a bit, but the idea was sufficient!

[Greg_Deckler]

@ThiemenSiemensm First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Also, see this: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community