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?
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?bcdobbs so sorry wrong version should only be 5 tables in total I replaced the file try again and let me know if you have any issues
https://1drv.ms/u/s!Ap_eXUht9lqhhT4VlI7mT6vHYeme?e=uEQn4J
so the goal is to somehow be able to have a measure that can dynamically filter the first union Join for the brands so that the averages change based on the filter context. The second table in the union join is ok. hope this makes sense.