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
Newcolator
Helper II
Helper II

Cumulating by two different variables

Hi! 

 

I am creating a cumulative total of value by day. Using this data.

 

Newcolator_0-1718632452943.png

 
I create a quick measure
 
Cumulative total by Day =
CALCULATE(
    SUM('Sheet1'[value]),
    FILTER(
        ALLSELECTED('Sheet1'[day]),
        ISONORAFTER('Sheet1'[day], MAX('Sheet1'[day]), DESC)
    )
)
 
And it works.
 
Newcolator_1-1718632565157.png

 

If I use the same DAX measure but show it by 3 day group it doesn't work. I assumed it would work because 3 day group is ordered the same as day. I thought cumulating by day would automatically work over 3 day group, but no.

 

Newcolator_2-1718632605140.png

 

I have to create a new DAX measure to calculate the running total by 3 day group.

 

Cumulative Total by 3 day group =
CALCULATE(
    SUM('Sheet1'[value]),
    FILTER(
        ALLSELECTED('Sheet1'[3 day group]),
        ISONORAFTER('Sheet1'[3 day group], MAX('Sheet1'[3 day group]), DESC)
    )
)
 
And that works.
 
Newcolator_3-1718632659669.png

 

Is it possible to do both of these with just one DAX expression? It would be useful not to have to duplicate all my dax measures when I want to cumulate over a different variable.

1 ACCEPTED SOLUTION
tharunkumarRTK
Solution Sage
Solution Sage

@Newcolator 

You can follow this pattern:

Cumilative = CALCULATE(SUM('Table'[value]), FILTER(ALLSELECTED('Table'[day], 'Table'[3 day group]), 'Table'[day] <= MAX('Table'[day]) && 'Table'[3 day group] <= MAX('Table'[3 day group])) ) 

 



Need Power BI consultation, hire me on UpWork .


If the post helps please give a thumbs up



If it solves your issue, please accept it as the solution to help the other members find it more quickly.




Tharun



View solution in original post

2 REPLIES 2
tharunkumarRTK
Solution Sage
Solution Sage

@Newcolator 

You can follow this pattern:

Cumilative = CALCULATE(SUM('Table'[value]), FILTER(ALLSELECTED('Table'[day], 'Table'[3 day group]), 'Table'[day] <= MAX('Table'[day]) && 'Table'[3 day group] <= MAX('Table'[3 day group])) ) 

 



Need Power BI consultation, hire me on UpWork .


If the post helps please give a thumbs up



If it solves your issue, please accept it as the solution to help the other members find it more quickly.




Tharun



Wonderful, it works. And presumably I can add more variables to the measure using the same pattern?

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.