Forum Discussion

dazzer360's avatar
dazzer360
Frequent Visitor
5 years ago

Cumulative Average affected by table filter

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 spe...
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
5 years ago

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's avatar
    dazzer360
    Frequent Visitor
    5 years ago

    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's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      5 years ago

      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] )
    )
)