cancel
Showing results 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

Helper II

Cumulating by two different variables

Hi!

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

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.

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.

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.

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
Solution Sage

@Newcolator

``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

2 REPLIES 2
Solution Sage

@Newcolator

``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

Helper II

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