Context:
I am calculating a survey average for the prior year across states, and I want to flag if a customer is in that state and gave a score lower than the state's annual average.
This is what I've got right now:
| State | PY Annual Avg | Customer ID | Customer Avg |
| AZ | 4 | 123 | 4 |
| AZ | 3 | ABC | 3 |
| AZ | 3 | 456 | 3 |
| CA | 4 | DEF | 4 |
| CA | 5 | 789 | 5 |
| CA | 3 | GHI | 3 |
So I want the PY Annual Avg to be a locked value based on just the State, but when I add the Customer ID to the table, it recalculates the measure to calculate the average for the customer, so I can't create the Y/N flag, because the PY Annual Avg column doesn't stay "locked" to the desired value.
This is what I want the output to look like:
| State | PY Annual Avg*DESIRED VIEW* | Customer ID | Customer Avg | **Desired Flag** |
| AZ | 3.33 | 123 | 4 | N |
| AZ | 3.33 | ABC | 3 | Y |
| AZ | 3.33 | 456 | 3 | Y |
| CA | 4 | DEF | 4 | N |
| CA | 4 | 789 | 5 | N |
| CA | 4 | GHI | 3 | Y |
I can't seem to use any windows functions, it gives me errors about scalar values. And I've tried using combinations of Keep/Allexcept/Remove filters but they don't produce the desired result (closest I get is that each customer ID gets duplciated across every state, which isn't accurate).
I unfortunately can't share my real data, but hopefully this is enough context to get some help?
Thanks in advance!
