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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors