Forum Discussion
samdep
Advocate II
Advocate IINPS Score with a Filter (Different Table)
Hi All - Can anyone help me debug my code -- I'm building a time series and want to show NPS (all clients), NPS (Client Group 1), NPS (Client Group 2), etc. The Client Group designation lives on...
samdep
When you use Variables as Expressions inside CALCULATE, they are not evaluated by the filters in CALCULATE, variables are constant.Try the following modified version:NPS Calc (Client Group 1) = CALCULATE ( DIVIDE ( VAR Promoter = CALCULATE ( COUNTA ( 'Simple Survey'[MASTER NPS FIELD 1] ), 'Simple Survey'[MASTER NPS FIELD 1] IN { "Promoter" } ) VAR Detractor = CALCULATE ( COUNTA ( 'Simple Survey'[MASTER NPS FIELD 1] ), 'Simple Survey'[MASTER NPS FIELD 1] IN { "Detractor" } ) VAR prom_minus_detractor = promoter - detractor RETURN prom_minus_detractor, VAR All_NPS = CALCULATE ( COUNTA ( 'Simple Survey'[MASTER NPS FIELD 1] ), 'Simple Survey'[MASTER NPS FIELD 1] IN { "Detractor", "Promoter", "Passive" } ) RETURN All_NPS ), 'Clients'[Client_Group] IN { "Client Group 1" } )
Fowmy
Super User
Super Usersamdep
When you use Variables as Expressions inside CALCULATE, they are not evaluated by the filters in CALCULATE, variables are constant.Try the following modified version:
NPS Calc (Client Group 1) =
CALCULATE (
DIVIDE (
VAR Promoter =
CALCULATE (
COUNTA ( 'Simple Survey'[MASTER NPS FIELD 1] ),
'Simple Survey'[MASTER NPS FIELD 1] IN { "Promoter" }
)
VAR Detractor =
CALCULATE (
COUNTA ( 'Simple Survey'[MASTER NPS FIELD 1] ),
'Simple Survey'[MASTER NPS FIELD 1] IN { "Detractor" }
)
VAR prom_minus_detractor = promoter - detractor
RETURN
prom_minus_detractor,
VAR All_NPS =
CALCULATE (
COUNTA ( 'Simple Survey'[MASTER NPS FIELD 1] ),
'Simple Survey'[MASTER NPS FIELD 1] IN { "Detractor", "Promoter", "Passive" }
)
RETURN
All_NPS
),
'Clients'[Client_Group] IN { "Client Group 1" }
)
samdepThis worked perfectly, thank you! I didn't realize what you'd said about variables being constant, so that's good to know - thank you!