Summarize table with dynamic filtering

[PBI-Guy]

Hello Team, I need some major help to solve this issue. I have a summarized calculated table that I need to be able to filter dynamically. Below is a sample of the calculated table that I have a union joining on. The first table in the union is "Brand EPA" and is an average from the full Brand EPA table. 

Below is the full Brand EPA table where the summarized calculated table is pulling from however I need a way to have the results be dynamic when I filter on Sex, Age, Education, Income and BrandOwn. This will change the average from being static to dynamic in the summarized table.

Thanks all,

Sean

[AlexisOlson · Accepted solution]

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.

[PBI-Guy]

Awesomeness!! thanks to @AlexisOlson and @bcdobbs  for your help with this one.

[bcdobbs]

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.

[PBI-Guy]

@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? 

[bcdobbs]

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!

[PBI-Guy]

@bcdobbs  

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

[bcdobbs]

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?

[PBI-Guy]

@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.

[bcdobbs]

Really sorry I've had a look but I can't follow what you're trying to achieve.

I can see the two tables and the union join between them but they look like very different sets of data so not sure what you're trying to achieve. Can you give a numeric example of an output you would expect based on a particular filter.

[PBI-Guy]

@bcdobbs ok, I understand but I will try to break it down step by step as easily as I  can because I do have a hard time explaining it so here goes.

Step 1: The first table is the Survey table which is the table that has all the data for the master survey taken by various respondents. The Survey has 4 questions that have various responses.

The 3 element fields are scores for each survey respondent.

Step 2: The second table is the profile table that has static data for various user profiles. This table is not related to that Survey table but does have the same 3 element fields. However, this table is static and does not change. 

Step 3: This is where I need the magic to happen the profile calculated table is the union between the profile table and the survey table to create a new profile table that now includes the summarized average for the 3 elements from the surveys table. So the final output should be a new table or measure that combines the static profile table with the 3 elements and the aggregated brand as shown in the below screenshot.

Filtering: The second thing that I need and where I am stuck is the ability to filter the Survey questions and have that filter adjust the summarized Brand elements.

I hope this helps, its difficult to explain but that is the best I can do. please let me know if you need more context.

.

[AlexisOlson]

What you're asking for is a dynamic calculated table, which is not possible. So maybe you can explain what your end goal is with this table rather than defining the table as your end goal. What information are you ultimately trying to answer or visualize?

[PBI-Guy]

@AlexisOlson a thanks for chiming in on this. Essentially I need to be able to some how combined the all the static values from the profile table (profile | element1 | element2 | element3) with the aggregated averages for the same three elements from the survey respondents. Without getting too much into the nitty-gritty I'm using a Euclidean distance formula that shows me the distance from the survey elements to the profile elements. An example of this would be for all the respondents in the survey under brand number one if we were to look at their three elements (which could change dynamicly based on the filtering of the question in the survey) what would the closest distance be from the profiles in the other table essentially ranking them by distance order. 

[AlexisOlson · Accepted solution]

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.

[PBI-Guy]

@AlexisOlson  my goodness thanks so much! this gets me much closer now. the only issue I have is I need to plot it on a scatter chart like below.

And I also need to make the top N variable between 5-10

Thanks again Alexis

[AlexisOlson]

Top N what though? What measure does it affect (or do you just want to limit the rows showing up on the right)?

[PBI-Guy]

@AlexisOlson It would affect the "closest profile" measure to limit how many rows are showing (Dynamic between 5-10). 

[bcdobbs · Accepted solution]

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 think @AlexisOlson deserves the solution on this one. I just wrapped his work in a rankx.

[bcdobbs]

Ok, starting to understand! Not sure it's statistically valid (think you'd at least need the number of respondents for each profile type).That said I'll send you an example measure later this morning that I think does what you're asking.

[PBI-Guy]

Awesome! I await your reply.

[bcdobbs]

Sorry @PBI-Guy I'm going to have to check out of this one. Have had another look but I just don't understand what you're trying to achieve. 

@AlexisOlson don't suppose you could have a look, I'm probably missing something obvious!