Cumulative Average affected by table filter

[dazzer360]

Hi,

I am trying to get my head around the below table behaviour:

Essentially I need the weekly average of the data but then also the cumulative average, this appears to be fine if I select a specific category:

However when I then include multiple categories incorrect averages are then given for both categories:

This is the DAX I am using (I convert to HH:MM:SS in a seperate measure)

A2H_Avg_Year_To_Date =

CALCULATE(

AVERAGE(Assignments_Detail[A2H_Secs]),

FILTER(

ALLSELECTED(Assignments_Detail),Assignments_Detail[RespDateDestination] <= MAX(Assignments_Detail[RespDateDestination])

))

I've tried a number of different things but I can't work out what I need to do so any help would be appreciated!

[AlexisOlson]

This is because the ALLSELECTED removes the category context. I'd try making the ALLSELECTED more targeted by referencing just the date column rather than the whole table.

A2H_Avg_Year_To_Date =
CALCULATE (
    AVERAGE ( Assignments_Detail[A2H_Secs] ),
    FILTER (
        ALLSELECTED ( Assignments_Detail[RespDateDestination] ),
        Assignments_Detail[RespDateDestination]
            <= MAX ( Assignments_Detail[RespDateDestination] )
    )
)

[dazzer360]

Hi,

Thank you for you reply. Unfortunately that leads to the situation where every Week Ending column gives the same average and it basically seems like it isnt doing the cumulative bit:

Do you have any other ideas?

[AlexisOlson]

I'd have expected that to work if [RespDateDestination] is the column you are using for the date column headers in your visual. If it's not, then whatever dimension you are using there should be in ALLSELECTED instead.

I suppose you can approach it from the other side by adding back in the context you do want, though this isn't quite convenient if you want to use a different dimension for your row headers.

A2H_Avg_Year_To_Date =
CALCULATE (
    AVERAGE ( Assignments_Detail[A2H_Secs] ),
    VALUES ( Assignments_Detail[Category] ),
    FILTER (
        ALLSELECTED ( Assignments_Detail ),
        Assignments_Detail[RespDateDestination]
            <= MAX ( Assignments_Detail[RespDateDestination] )
    )
)