Creating Regions from a Group of Cities

[AaronGlenn10]

I'm trying to create regions from a group of cities - for example, the Dallas-Fort Worth region is comprised of ~200 cities.  What is the best approach to accomplish?

Thank you so much!

~AGS

[mahoneypat · Accepted solution]

You could bring in a table with city and state columns and merge that into your data.  Or you could make a Groups column yourself.

Use grouping and binning in Power BI Desktop - Power BI | Microsoft Docs

Regards,

Pat

[v-jayw-msft · Accepted solution]

Hi @AaronGlenn10 ,

If you already have a group column, you could use ALLEXCEPT() function to get the average for each group.

https://docs.microsoft.com/en-us/dax/allexcept-function-dax 

For example:

average = calculate(average('table'[value]),allexcept('table'[group]))

Best Regards,

Jay

[mahoneypat · Accepted solution]

You could bring in a table with city and state columns and merge that into your data.  Or you could make a Groups column yourself.

Use grouping and binning in Power BI Desktop - Power BI | Microsoft Docs

Regards,

Pat

[AaronGlenn10]

I've created a group but it doesn't work when I try to use as a visualization filter.

[vanessafvg]

what will your group be based on?   State?  county?  

[AaronGlenn10]

I'm only focused on one state - Texas.  The regions [groups] will be based on cities.  Need to create four regions - DFW, Austin, San Antonio, and Houston.  The DFW region [group], for example, will have 200 cities.

Does that help?

[vanessafvg]

as per @mahoneypat  I would go with the grouing option.

[AaronGlenn10]

How would I calculate an average across this group:

Same Store TTM Delta % for ABILENE & ACTON & ADKINS & ALAMO & ALAMO HEIGHTS & ALBA =

CALCULATE(

    [Same Store TTM Delta %],

    'TABC'[location_city (groups) 2]

        IN { "ABILENE & ACTON & ADKINS & ALAMO & ALAMO HEIGHTS & ALBA" }