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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
boa
Helper I
Helper I

Running totals for categories and years

Hello

 

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

This is a view of my data:

boa_0-1661781413152.png

 

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:
boa_1-1661781719859.png

 

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.

boa_2-1661782104503.png

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

 New Table:

boa_3-1661782429271.png

 

Anyone who can help me?

 

 

 

 
2 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

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] )
    )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

Hi,

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

I fixed two measures.

Thank you.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

5 REPLIES 5
boa
Helper I
Helper I

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

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:

boa_0-1661853280843.png

Result without filter:

boa_1-1661853611578.png

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

 

  

Hi,

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

I fixed two measures.

Thank you.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Hi,

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

Thank you.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Jihwan_Kim
Super User
Super User

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] )
    )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.