Forum Discussion
Summarize table with dynamic filtering
OK. We can certainly compute distances without needing an extra calculated table in the model. For example,
Closest Profile = VAR E1 = AVERAGE ( Survey[Element1] ) VAR E2 = AVERAGE ( Survey[Element2] ) VAR E3 = AVERAGE ( Survey[Element3] ) RETURN MINX ( TOPN ( 1, Profile, ( E1 - Profile[_Element1] ) ^ 2 + ( E1 - Profile[_Element1] ) ^ 2 + ( E1 - Profile[_Element1] ) ^ 2, ASC ), Profile[Profile] )You can make all this dynamic without a calculated table:
See attached.
Kudos to AlexisOlson for asking the right question to move this forward!
Rather than TopN do what you want:Avg Distance Rank = RANKX( ALLSELECTED(Profile[Profile]), [Avg Distance], , ASC, Dense )and use it in the visual filter?
A calculated table (and calculated columns) are always formed when the model is refreshed. So by definition are static.
Your options are:
1) Change the granualarity of your summarised table by adding in Sex, Age, Education, Income and BrandOwn. This means that the average is pre calculated for every combination.
2) Switch away from a calculated table and make use of measures which are dynamic.
bcdobbs I originally thought that the use of measures would be better however I don't have the know-how to achieve that hence why I took the calculated table path. Do you have any recommendations?
Ok, so if you just need a straight average that responds to filters in visuals you can go really simple.
1) Right click your table and click create measure.
2) Add measures for each summary you need.
eg
Avg Potency = AVERAGE(TableName[Potency])
Then add a table visual and add your normal columns and the measures at the end.
If you then have slicers for the other things everything will update.
If your requirements are more complex are you able to send a demo file and a requested output. Will happily write some measures for you!
I believe what I'm looking for might be a little bit more complex so I will post a sample file asap
