How Do I calculate Avg YOY Percent for a city VS Avg YOY Percent for the state where the city is?

[Chuky01]

Though I created a sample workbook, I'm not sure how to attach it with this questions, please let me know if there is a way. Here is my question. Using the Sample superstore dataset as an example, I want to create a table that can be filtered for each region. When I filter for Central as an example, I want to show the following - 

1. Each city and state in the region
2. The Average YOY Sales (%)  for that city vs Average YOY Sales for the state to which the city belongs
3. The Average YOY Sales (%) for that city vs Average YOY Sales for the region to which the city belongs

It could also be Month over Month Averger Percent change as well. 

I hope this makes sense. Thanks in advance.

[Jihwan_Kim]

Hi, @Chuky01 

Please correct me if I wrongly understood your question.

If you want to calculate the State level number, you can remove the city-level filter inside your measure.

If you want to calculate the Region level number, you can remove the State-level-filter inside your measure.

If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with more accurate measures.

thanks.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Chuky01]

Hi @Jihwan_Kim here is a link to file. Please let me know if you are able to access it. 

https://www.dropbox.com/s/3xm1deo85a1tui2/Data%20Model.pbix?dl=0

[Jihwan_Kim]

Hi, @Chuky01 

Please check the below two measures and link down below whether those are what you are looking for.

https://www.dropbox.com/s/7irqv59aah2ky1u/Data%20Model.pbix?dl=0 

calc Avg YOY% StateLevel =

CALCULATE( [calc Avg YOY %], ALLSELECTED(Location[City]))

calc Avg YOY% RegionLevel =

CALCULATE( [calc Avg YOY %], ALLSELECTED(Location[State], Location[City]))

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Chuky01]

@Jihwan_Kim So the formula above works perfect on the Superstore data. However now I'm trying to use it on my dataset at work and it's not quite producing the result I wanted lol. I may not have presented the correct scenario in my question.Let try and clarify.

The data I'm working has Region, City and Category. I am trying to find the Avg YOY % for each city, vs the Avg YOY % for all cities within the same category, within the same region. I feel like I just need to tweek your formula abit, but I'm not sure how. See screenshot for more clarity.

Looking at Aberdeen for example, I want to calculate Avg YOY % (-1.2%) vs the Avg YOY % for Category POP6 in the Central Region (-1.3%), and also vs Avg YOY for the Central region (-3.9%)

[Jihwan_Kim]

Hi, @Chuky01 

Thank you for your feedback.

In my opinion, you can simply replace STATE -> CATEGORY...

If it is OK with you, please share this sample pbix file's link, then I can try to look into it.

Thank you.

[Chuky01]

@Jihwan_Kim Here is some sample data 

https://www.dropbox.com/s/kozey5drx1jm4dq/Sample%20Data.pbix?dl=0

[Jihwan_Kim · Accepted solution]

Hi, @Chuky01 

I think the reason is, perhaps there are the same category names in different regions.

Please check the link down below, whether it is correctly done.

For the one measure, I created a bit different way to achieve the result.

calc Avg YOY RegionLevel =

CALCULATE( [calc Avg YOY (%)], ALLEXCEPT('00 City List', '00 City List'[Region]))

https://www.dropbox.com/s/go70slf5fssui5a/Sample%20Data.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Chuky01]

@Jihwan_Kim Thank you so much.