Multiple IF or Case statement for metric presentation

Question

Have a scorecard where the metric may be a total or an average. Based on that setting, trying to build formula rich text to show the appropriate data.

Got half of it working for Total. How would I expand to support Measurement being an Average or another measurement type?

If([Measurement] ="Total",

If ([2022 Actual YTD] < [2022 Target Low],
Case([Metric Type],
"Currency", "<span style='color: #FF0000'>"&"$"&ToFormattedText(Round([2022 Actual YTD],0.01),"none_dot")&"M"&"</span>",
"Percent", "<span style='color: #FF0000'>"&ToFormattedText(Round([2022 Actual YTD],0.01),"comma_dot")&"%"&"</span>",
"Number", "<span style='color: #FF0000'>"&ToFormattedText(Round([2022 Actual YTD],0.01),"none_dot")&"</span>",
""),

If ([2022 Actual YTD] >= [2022 Target High],
Case([Metric Type],
"Currency", "<span style='color: #228B22'>"&"$"&ToFormattedText(Round([2022 Actual YTD],0.01),"none_dot")&"M"&"</span>",
"Percent", "<span style='color: #228B22'>"&ToFormattedText(Round([2022 Actual YTD],0.01),"comma_dot")&"%"&"</span>",
"Number", "<span style='color: #FF0000'>"&ToFormattedText(Round([2022 Actual YTD],0.01),"none_dot")&"</span>",
""),

//otherwise
"No Actuals"))
)

------------------------------
BRIAN HINSHAW
------------------------------

brianhinshaw · Answer

A QB Ninja helped clear this up for me and simplify the code.

If(IsNull([2022 Actual YTD]),"No Actuals",
Case(
[Measurement],
"Total",
"<div style='color: " & If([2022 Actual YTD] < [2022 Target Low],"#FF0000",[2022 Actual YTD] >= [2022 Target High],"#228B22") & "'>"&If([Metric Type]="Currency","$")&ToFormattedText(Round([2022 Actual YTD],0.01),"none_dot")&Case([Metric Type],"Currency","M","Percent","%")&"</div>",
"Average",
"<div style='color: " & If([2022 Actual AVG YTD] < [2022 Target Low],"#FF0000",[2022 Actual AVG YTD] >= [2022 Target High],"#228B22") & "'>"&If([Metric Type]="Currency","$")&ToFormattedText(Round([2022 Actual AVG YTD],0.01),"none_dot")&Case([Metric Type],"Currency","M","Percent","%")&"</div>",
ToText([2022 Actual AVG YTD])
)
)

------------------------------
BRIAN HINSHAW
------------------------------

Forum Discussion

Multiple IF or Case statement for metric presentation