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
https://1drv.ms/u/s!Ap_eXUht9lqhhT4VlI7mT6vHYeme?e=ZBN9dl
Please see attached one drive link to the pbix file. I want to be able to summarize the brand dynamically so that when I filter on the survey questions I gives me the proper aggregated averages for the elements 1 -3 in the 02Profile_Calculated table which feeds back into the 01MergeDataSet which is the table that drives my visuals and filters. I hope this makes sense because I do have a hard time explaining it but if you need more I will do what I can
I can't see a 02Profile_Calculated in the file you've sent.
At first glance you have lots of dimension tables like Education_tbl but they're not related to your main fact tables. Am I looking at the right version?