Forum Discussion
Using a Measure Across Fields to Calculate a flag
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!
3 Replies
but when I add the Customer ID to the table, it recalculates the measure to calculate the average for the customer,That is not evident in your sample data. Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
