Dynamic filtering for related sub data

[PBI-Guy]

Here goes...this is my first post on the forum so please forgive any missed etiquette. I hope I can explain this as best as humanly possible.

Goal: Multi-Category Filters in table 1 are reflected in the aggregated summary average for the same category in table 2.

Step 1: 

• To start I have 2 tables Table 1 is a survey result with all respondents along with their calculated scores (Elements 1-3)
• Table 2 is a list of Identities that have elements 1-3 scores almost like a personality score.  and acts as a reference table, however, the brand is not natively a part of table 2. This is where I'm am having the challenge.
• For me to perform the calculations I need, I had to create a calculated table using the Summarize DAX function (table 3) that averages elements 1-3 and gives me a summarized total for each brand in table 1.

• I then use the Generageall DAX function to bring together table 3 summary into table 2 so that I can have the brand summary scores now a part of table 2 identity in a new combined table (let's call it table 2.2)
•  
• The problem that is I can't seem to fix is that Table 3 Averages all 3 elements from Table 1  however it is not responsive to any filters in table 1. For example, if I would like to filter on question 1 and have the updated values recalculated and reflected back on table 2 but it does not update based on the filter it just gives me the same summarized calculation as before but I need it to be dynamic so that I can apply various filters in my visuals for the proper display.

I literally do not have the mental know-how ohn how to achieve this and I have been trying for the last 6 days and am just about ready to give up so any assistance you can provide would be greatly appreciated.

Thanks,

Sean

[PBI-Guy]

@VahidDM is the sample file helpful at all? 

[v-easonf-msft]

Hi, @PBI-Guy 

Slicers have no effect on the generated calculated table.

You may need to calculate the value in temporary table (measure).

Best Regards,
Community Support Team _ Eason

[PBI-Guy]

@v-easonf-msft that's what I thought but I don't know how to do that. Any insight you can provide will be helpful and greatly appreciated

[PBI-Guy]

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.

Cheers

Sean

[PBI-Guy]

@VahidDM thanks so much for the follow-up...I will prep a sample file with the pbix file.

[VahidDM]

Hi @PBI-Guy 

Can you post sample data as text and expected output? Do you want a table with Brand items and Average for each Element? 


please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[PBI-Guy]

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.