Average NPS formula by Fiscal year not working

[romovaro]

HI all

I have an issue with one of my NPS formulas:

First I have a formula to get the average.

Client_Scores Event =

CALCULATE(

    AVERAGE('ResponsesFY25'[LTR - Event]),

    ALLEXCEPT('ResponsesFY25','ResponsesFY25'[Client Account Name])

)

And then I have a formula to see if customer is "D / P / Pr" during.

Event Score Status =

IF('ResponsesFY25'[Client_Scores Event] <= 6.99 , "Detractor",

IF('ResponsesFY25'[Client_Scores Event] <= 8.99, "Passive",

IF('ResponsesFY25'[Client_Scores Event] <= 10, "Promoter",

"TBD"

)))

The problem I have is that formula is not updating results if different FY are selected.

Customer in table (left) is passive because average of ALL scores is btw 7 - 8,99

BUT....Same Customer (table right) if selecting FY24. Scores is 47 and Average is 9.4...then PROMOTER (not passive)

any suggestion? thanks,

[johnt75 · Accepted solution]

The problem is that ALLEXCEPT is removing the filters from the expanded table, which presumably includes the date table. You can modify your code to

Client_Scores Event =
CALCULATE (
    AVERAGE ( 'ResponsesFY25'[LTR - Event] ),
    ALLEXCEPT ( 'ResponsesFY25', 'ResponsesFY25'[Client Account Name], 'Date'[FY] )
)

You will need to include in the ALLEXCEPT any columns in the date table which may be used to sort the fiscal year column, if it is not sorted by itself.