Running totals for categories and years

[boa]

Hello

I'm totally new to Power BI. I have a problem with calculating running totals. 

This is a view of my data:

I have tried a lot of dax-expressions and this expression works for 1 year (i use a filter on start_year)

running total = calculate(sum('Blad1'[Count]);filter(allselected('Blad1');'Blad1'[catnr]<=SELECTEDVALUE('Blad1'[catnr])))

Result:

But now I want a matrix with the categories and years in (so no filter on year) and this is the result. I tried different dax expressions with ALL, ALLSELECTED, MAX, EARLIER,... but i can't find the solution.

I have also made a new table (summarize) and tried it with this table but non of the expressions is working.

 New Table:

Anyone who can help me?

[Jihwan_Kim · Accepted solution]

Hi,

Could you please try something like below whether it suits your requirement?

running total =
CALCULATE (
    SUM ( 'Blad1'[Count] );
    FILTER (
        ALLSELECTED ( 'Blad1' );
        'Blad1'[catnr] <= SELECTEDVALUE ( 'Blad1'[catnr] )
            && 'Blad1'[Start_year] = MAX ( 'Blad1'[Start_year] )
    )
)

[boa]

@Jihwan_Kim Thank you! It works, you made my day!

[boa]

Hi,

I try now to calculate the percentage of the running total. The DAX expression works again if I filter the data but not without filter on years.

DAX expression:

Percentage running total =

CALCULATE (

    SUM ( 'Blad1'[Count] );

    FILTER (

        ALLSELECTED ( 'Blad1' );

        'Blad1'[catnr] <= SELECTEDVALUE ( 'Blad1'[catnr] )

            && 'Blad1'[Start_year] = MAX ( 'Blad1'[Start_year] )

    )

)/CALCULATE(SUM('Blad1'[Count]);ALLSELECTED('Blad1'))

Result with filter:

Result without filter:

I tried different expressions but I only get errors :-(. 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the attached file.

I fixed two measures.

Thank you.

[Jihwan_Kim]

Hi,

Please share your sample pbix file's link, and then I will try to look into it.

Thank you.

[Jihwan_Kim · Accepted solution]

Hi,

Could you please try something like below whether it suits your requirement?

running total =
CALCULATE (
    SUM ( 'Blad1'[Count] );
    FILTER (
        ALLSELECTED ( 'Blad1' );
        'Blad1'[catnr] <= SELECTEDVALUE ( 'Blad1'[catnr] )
            && 'Blad1'[Start_year] = MAX ( 'Blad1'[Start_year] )
    )
)