How to sum over all rows with filters based on row values and respecting visual structure

[Anonymous]

Hi Guys,

Hope you can help me out here.

My aim is to present an account plan using the correct sums for each level in the account plan. The model is in Danish so I'll translate to English and put the Danish names in ()

I have three tables Calendar (called Bogføringsdato), Postings (Posteringer), Accounts (Kontoplan)

The Accounts table has the full account plan. Each sum is also a row in the table. For every one of these rows, a column specifies the roll-up logic and other columns contains the specific. E.g. when the [Type] column contains the value "Sumfra" it means the roll-up logic should sum from a specific account number to the current account number. 

I've actually got it working but without modeling the relationship between the postings and the account plan. Hence I'm working on adding the relationship and altering the DAX to reflect this. This is proving a lot harder than expected. 

So far the DAX formula is calcuating the sum correctly over account numbers, but all other filters are ignored. 

The sum for Revenue in total (Omsætning i alt) is the sum of revenue for all months, rather than for the month in the column. The correct sums would have been 2.000 and 62.000

The data model looks like this:

Accounts (Kontoplan), Postings (Posteringer), Calendar (Bogføringsdato)

The Dax for Calculating [Sumfra2 Saldo ÅTD] is

Sumfra2 Saldo ÅTD = IF(MIN('Kontoplan'[Type])="Sumfra"
    ;CALCULATE(SUM('Posteringer'[Bogført beløb DKK_orig])
                ; FILTER(all('Posteringer')
                    ;'Posteringer'[KontoNr]>MIN(Kontoplan[Interval1Fra]) && 'Posteringer'[KontoNr] < MIN(Kontoplan[Kontonr.])))
    ;BLANK())

The intension is to only do the calculation when the Account from Accounts is of Type="Sumfra".  When this is the case sum the posted amount ([Bogført beløb DKK_orig]) sum over all accounts where the account number is between [Interval1Fra] and the current Account number ('Kontoplan'[Kontonr.]).

The ALL function is most likely wrong but I cannot find another way sum over all posting and respect other filters and visual structure. 

As I mentioned I got a model where the logic works, but then there is no relationship between Postings (posteringer) and the account plan (in this DAX the table is called "Regnskabsopstilling" instead of "Kontoplan"). This is the DAX for this similar measure:

Sumfra Saldo ÅTD = CALCULATE(SUM('Posteringer'[Bogført beløb DKK_orig])
                    ; FILTER('Posteringer'
                    ; 'Posteringer'[Måned] = CALCULATE(MAX('Bogføringsdato'[Månedsnr])) 
                        && 'Posteringer'[År] = CALCULATE(MAX('Bogføringsdato'[År])) 
                        && 'Posteringer'[KontoNr] >= CALCULATE(MIN('Regnskabsopstilling'[Interval1Fra])) 
                        && 'Posteringer'[KontoNr] < CALCULATE(MIN('Regnskabsopstilling'[Kontonr.])) 
                        && CALCULATE(MIN('Regnskabsopstilling'[Type])="Sumfra") ))

Hope you can help me out 🙂