Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ThiemenSiemensm
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
ThiemenSiemensm_1-1673619567144.png

 

Working manual DAX expression.

ThiemenSiemensm_2-1673619774637.png

 

 

The Matrix

 

ThiemenSiemensm_0-1673619341698.png

 

1 ACCEPTED SOLUTION
TomasAndersson
Solution Sage
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:

TomasAndersson_0-1673622921199.png

 



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! 

View solution in original post

3 REPLIES 3
TomasAndersson
Solution Sage
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:

TomasAndersson_0-1673622921199.png

 



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! 

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

Greg_Deckler
Super User
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

 

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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