cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

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?

3 REPLIES 3
Super User
``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.

New Member

I've edited the table - like I said in the post, it's just mock data so I'm trying to explain the issue I'm having, since I can't really show it.

Super User

I cannot assist you if you are unable to provide sample data. I hope someone else can help you further.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors