HELP!! Measure show comparison between selected values and other categories

[jts_]

Hi, I have a simple table that contains state, county, and growth rates.
I need to create a measure in Power BI that allows the visualization would change dynamically for comparison.

In the project scope, there are 3 states in total. I want each state's average growth rate to stay on the visualization all the time.

In addition, if there is a county is being filtered, this measure would return the average growth rate of each state PLUS the growth rate of the selected county.

Below is an example of the data:

State	County_Name	CountyState	GrowthRate
Arkansas	Baxter County	Baxter County Arkansas	2.0%
Arkansas	Ashley County	Ashley County Arkansas	3.0%
Arkansas	Arkansas County	Arkansas County Arkansas	1.5%
Kentucky	Adair County	Adair County Kentucky	1.7%
Kentucky	Allen County	Allen County Kentucky	3.2%
Kentucky	Anderson County	Anderson County Kentucky	6.8%
Virginia	Accomack County	Accomack County Virginia	10.0%
Virginia	Albemarle County	Albemarle County Virginia	0.5%
Virginia	Alleghany County	Alleghany County Virginia	6.0%

If Allen County is filtered, I'd like this measure to show the average rate of each state with Allen County in the comparison.

Arkansas	6.5%
Kentucky	3.9%
Virginia	5.5%
Allen County	3.2%

Explainer in photo format:

Thank you in advance!! I appreciate all your insights!

[Anonymous · Accepted solution]

Hi @jts_ ,

Since dynamic tables are not supported in PBI Desktop, your expected result cannot be achieved by presenting the State column and the selected County_Name column on a single column.

(1) My test data is the same as yours.

(2) We create a new table.

Table 2 = 
VALUES('Table'[State])

(3) We can create a measure.

Measure = 
var _rate=CALCULATE(SUM('Table'[GrowthRate]),FILTER(ALL('Table'),'Table'[State]=MAX('Table 2'[State])))
var _rows=COUNTROWS(FILTER(ALL('Table'),'Table'[State]=MAX('Table 2'[State])))
return DIVIDE(_rate,_rows,0)

(4)Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[amitchandak]

@jts_ , You need a disconnected table for that. Assume you have measure m1

then have new measure

m2= calculate([m1], filter(Table, Table[State] in values(State[State]) )

or

m2= calculate([m1], keepvalues( Table[State] in values(State[State]) )

Compare Categorical Data Using Slicers, Compare Two Brands/Categories/Cities: https://youtu.be/exN4nTewgbc

Need of an Independent/disconnected Table in Power BI - Exclude: https://youtu.be/lOEW-YUrAbE