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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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