Using Max(Aggr(Count(..))) is not providing desired results

[Anonymous]

Hi everyone! I'm trying to do a ColorMix1 for a table, but I'm stumped.

I have a table in my app that looks something like this:

Engine	Issues Rate
Engine 3	1.65
Engine 1	1.01
Engine 2	0.73
...	...

Where '% of Issues' is Count(Distinct [Issues])*100/$(AllIssues). So a pretty basic ratio (normalized by every 100 engines produced).

To use ColorMix1, I've figured out that the value has to be between 0 and 1...I can't figure out how to normalize these values such that Engine 3 becomes 1 for the ColorMix1 function.

Essentially, the math to get Engine 3 Issue Rate from 1.65 to 1.0 and scale the rest of the values accordingly is the issue.

To get around this, I was trying to make the Aggr function work such that:

=ColorMix1(Count(Distinct [Issues])*100/$(AllIssues) / Max({$<Engine=>}Aggr(Count({$<Engine=>}Distinct [Issues])*100/$(AllIssues),Engine)),LightGreen(),LightRed())

where I could get the Max Engine value of the expression. This doesn't return what I'm expecting; I'd like the maximum value of the Aggr function's expression. The Max of the Aggr function is only reporting the Max relative each engine.

How do I make the denominator a static value, especially if I can't assigned a variable using the Aggr function in the load script?

Thanks!

[sunny_talwar · Accepted solution]

Can you try this:

=ColorMix1(Count(Distinct [Issues])*100/$(AllIssues) / Max(TOTAL {$<Engine=>}Aggr(Count({$<Engine=>}Distinct [Issues])*100/$(AllIssues),Engine)),LightGreen(),LightRed())

[Anonymous]

Wow, that was simple! Works exactly how I wanted it to!

Thanks Sunny T!!

[ramoncova06]

is your highest value always going to be 1.65 ?

if it is why not just divided it by that

=ColorMix1((Count(Distinct [Issues])*100/$(AllIssues))/1.65),LightGreen(),LightRed())

if it might change then I would recommend adding it as a variable instead

[Anonymous]

It won't always be 1.65...that's the problem. Based on selections and when I reload the app with new data every few days, that number will always change. That's the primary reason I wanted to have it come from an Aggr function.

[ramoncova06]

ok, then in that case aggr was the best option