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
hluque
Frequent Visitor

Cumulative total not working

Hello,

 

I'm facing an issue with my cumulative total that is surely related to the applied filters. I need to calculate the cumulative revenue and expenses by month and by client.

 

I've used the following formula but it only works when showing the total, if I add a client filter it won't work, it will simply show the total revenue of each month for that client:

 

CALCULATE([IMS_Revenue];filter(ALL('Raw Data');'Raw Data'[Year/month]<=MAX('Raw Data'[Year/month]))

Alternatively, I've used this one with ALLSELECTED that works when I select a client from the visual level filters but not if I want to use a it on matrix:

 

CALCULATE([IMS_Revenue];filter(ALLSELECTED('Raw Data');'Raw Data'[Year/month]<=MAX('Raw Data'[Year/month]))

This is how the tables are related:

Running total error_2.JPG

 

 

Can you help me here?

 

Thanks a lot.

1 ACCEPTED SOLUTION
hluque
Frequent Visitor

At the end I figured it out, I needed to use the calendar table. Thus it allows me to filter by any client:

 

IMS_Revenue acumulado = CALCULATE([IMS_Revenue];filter(ALL(Calendario);Calendario[Date]<=MAX(Calendario[Date])))

Regards.

View solution in original post

2 REPLIES 2
hluque
Frequent Visitor

At the end I figured it out, I needed to use the calendar table. Thus it allows me to filter by any client:

 

IMS_Revenue acumulado = CALCULATE([IMS_Revenue];filter(ALL(Calendario);Calendario[Date]<=MAX(Calendario[Date])))

Regards.

jthomson
Solution Sage
Solution Sage

You'll want to look into using ALLEXCEPT rather than ALL and indicate the field which contains your client names. ALL's going to ignore the filters you set on the clients unless you tell it otherwise

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors