Help needed: ALLSELECTED with the exception of one column

[simonm8008]

Fairly new at this, so there's probably a simple solution for my problem.

I have the following table where I am calculating an average of the last 5 sprints

I am using the following measure:

Average Velocity Last 5 Sprints = 
VAR MaxSprint = SELECTEDVALUE('All User Stories'[Sprint ID])
VAR SprintRange = MaxSprint - 4
RETURN
CALCULATE(
    AVERAGEX('All User Stories',SUM('All User Stories'[Story Points])),
    FILTER(ALLSELECTED('All User Stories'),
        'All User Stories'[Sprint ID] >= SprintRange && 'All User Stories'[Sprint ID] <=MaxSprint)
        )

However, what I actually want to do is to keep all filters EXCEPT for 'ADMS All User Stories'[Sprint ID].

Any help would be greatly appreciated!

[v-yetao1-msft]

Hi @simonm8008 

Did you get the result you want ? If yes , please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Community Support Team _ Ailsa Tao

[AlexisOlson]

You've got an odd mix of things going on here.

I'd recommend re-writing your measure like this:

Average Velocity Last 5 Sprints =
VAR MaxSprint = SELECTEDVALUE ( 'All User Stories'[Sprint ID] )
VAR SprintRange = MaxSprint - 4
RETURN
    CALCULATE (
        AVERAGE ( 'All User Stories'[Story Points] ),
        ALLSELECTED ( 'All User Stories' ),
        'All User Stories'[Sprint ID] >= SprintRange,
        'All User Stories'[Sprint ID] <= MaxSprint
    )

Or like this:

Average Velocity Last 5 Sprints =
VAR MaxSprint = SELECTEDVALUE ( 'All User Stories'[Sprint ID] )
VAR SprintRange = MaxSprint - 4
RETURN
    AVERAGEX (
        FILTER (
            ALLSELECTED ( 'All User Stories' ),
            'All User Stories'[Sprint ID] >= SprintRange &&
            'All User Stories'[Sprint ID] <= MaxSprint
        ),
        'All User Stories'[Story Points]
    )

I think the main problem you have is that the SUM inside AVERAGEX isn't transforming the row context of the iterator into filter context.

[Shaurya]

Hi @simonm8008,

Use:

Average Velocity Last 5 Sprints = 
VAR MaxSprint = SELECTEDVALUE('All User Stories'[Sprint ID])
VAR SprintRange = MaxSprint - 4
RETURN
CALCULATE(
AVERAGEX('All User Stories',SUM('All User Stories'[Story Points])),
FILTER(ALLEXCEPT('ADMS All User Stories'[Sprint ID]),
'All User Stories'[Sprint ID] >= SprintRange && 'All User Stories'[Sprint ID] <=MaxSprint))

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Previous Forecasts

[AlexisOlson]

ALLEXCEPT does the opposite of what OP is asking for (and requires a table for the first argument).