I'm trying to calculate a dissatisfied value based on two criteria, Name and Year. Below is the formula I've written. For some reason, the values for 2019 and 2020 don't generate. Can anyone here help me?
Highly Dissatisfied - Summary =
VAR TotalResponses =
CALCULATE(
COUNT('Combined Raw Data'[Rating Value]),
'Combined Raw Data'[Rating Value]<>blank(),
'Mapping Level 1'[Service Area]="Overall",
'Mapping Level 1'[Overall/Area]="Overall",
'Combined Raw Data'[Rating / Comment]="Rating"
)
VAR HighlySatis =
CALCULATE(
count('Combined Raw Data'[Rating Value]),
'Combined Raw Data'[Rating Value]=1||
'Combined Raw Data'[Rating Value]=2||
'Combined Raw Data'[Rating Value]=3||
'Combined Raw Data'[Rating Value]=4,
'Mapping Level 1'[Service Area]="Overall",
'Mapping Level 1'[Overall/Area]="Overall",
'Combined Raw Data'[Rating / Comment]="Rating"
)
Return
switch(
SELECTEDVALUE('Combined Raw Data'[SL ABB]),
"GBS",
switch(
SELECTEDVALUE('Year For Summary'[Year]),
2019,0.0747,
2020,0.0408,
2021, Divide(HighlySatis,TotalResponses),
2022, Divide(HighlySatis,TotalResponses)
),
"FIS",
switch(
SELECTEDVALUE('Year For Summary'[Year]),
2019,0.1562,
2020,0.0890,
2021, Divide(HighlySatis,TotalResponses),
2022, Divide(HighlySatis,TotalResponses)
),
"MDS",
switch(
SELECTEDVALUE('Year For Summary'[Year]),
2019,0.1592,
2020,0.1051,
2021, Divide(HighlySatis,TotalResponses),
2022, Divide(HighlySatis,TotalResponses)
),
"WSS",
switch(
SELECTEDVALUE('Year For Summary'[Year]),
2019,0.1652,
2020,0.1304,
2021, Divide(HighlySatis,TotalResponses),
2022, Divide(HighlySatis,TotalResponses)
),
"HRS",
switch(
SELECTEDVALUE('Year For Summary'[Year]),
2019,0.1513,
2020,0.0944,
2021, Divide(HighlySatis,TotalResponses),
2022, Divide(HighlySatis,TotalResponses)
),
"CAS",
switch(
SELECTEDVALUE('Year For Summary'[Year]),
2019,0.1261,
2020,0.0701,
2021, Divide(HighlySatis,TotalResponses),
2022, Divide(HighlySatis,TotalResponses)
),
"SAS",
switch(
SELECTEDVALUE('Year For Summary'[Year]),
2019,0.2105,
2020,0.1413,
2021, Divide(HighlySatis,TotalResponses),
2022, Divide(HighlySatis,TotalResponses)
)
)
