Forum Discussion
Helper IHELP!! Measure show comparison between selected values and other categories
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!
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.
2 Replies
- amitchandak
Super User
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
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.
