cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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

1 ACCEPTED SOLUTION
Solution Sage

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 3
Solution Sage

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!

Frequent Visitor

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

Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...