Sort order breaks running total measure!!

DJSwezey — Tue, 02 May 2023 14:14:02 GMT

I have a simple sales table below: 'Sales'

This thrown into a simple Matrix looks like this:

My objective is to move the TOTALs to the front of the matrix (left) and then to create a measure that results in a running total week over week of the Goal amounts. To do this I created a new table ( 'Week Order' ) to sum all data across all date points. See picture below:

I then related the two tables on 'Sales'[Week] and 'Week Order'[Week Date]

This allows me to use 'Week Order'[Week Of] as the column object in my matrix.

Now I need to create a measure to calculate the running total of the Goal amounts.

Goal Running Total = CALCULATE( sum(Sales[Goal]), FILTER( all('Week Order'[Week Of]), 'Week Order'[Week Of] <= MAX('Week Order'[Week Of]) ) )

When I throw this measure into the matrix everything works fine before sorting [Week Of]. You can see below that the running total for goals adds each week. 4/10/23 = 250, 4/17/23 = 100 + 250, 4/3/2023 = 125+ 100 + 250

Now, when I sort [Week Of] by [Order] the measure no longer compiles the Goal total each week.

This is the problem I need help solving.

Re: Sort order breaks running total measure!!

lbendlin — Tue, 02 May 2023 23:48:50 GMT

Try this version

Goal Running Total = var mx = MAX('Week Order'[Week Of]) return CALCULATE( sum(Sales[Goal]),'Week Order'[Week Of] <= mx)

topic Re: Sort order breaks running total measure!! in DAX Commands and Tips

Sort order breaks running total measure!!

Re: Sort order breaks running total measure!!