Summing from multiple tables AFTER slicers

Hi,

I have several tables (15+) which look like this:

Table 1

Question	Weighting	category	Response
1	7	A	Yes
1	0	A	No

Table 2

Question	Weighting	Category	Response
2	10	A	Yes
2	0	A	No

Table 3

Question	Weighting	Category	Response
1	8	B	Yes
1	0	B	No

etc.

I have a slider for each table which the user can select between yes and no for. This returns a weighting.

What I want to do is create a table which compiles all the weightings for each category. For the above, it would look something like this if the slicer is set to Yes for all three questions:

Category	Score
A	17
B	8

I'm having trouble creating this final table, though.

I'd really appreciate any help with this.

Many thanks

Need Help

PaulDBrown
4 years ago
Here is a way using the appended tables. You need to create Yes/no Tables for every question and link them to the appended table using inactive relationships. This is how the model looks like:

Create measures for every question as follows:

Sum Weight 1 = CALCULATE(SUM(Append1[Weighting]), USERELATIONSHIP(Append1[Response],'Resonse 1'[Response 1]))
Sum Weight 2 = CALCULATE(SUM(Append1[Weighting]), USERELATIONSHIP('Append1'[Response],'Resonse 2'[Response 2]))

Set up a slicer using the corresponding Response table and add the measure for each response. Select the tabel visual and set the filter for the corresponding question.

to get

I've attached the sample PBIX file
Appended Tables.pbix36 KB

9 Replies

VahidDM
Super User
4 years ago
Hi zcec

In Power Query, first Append all tables:

Then in the new table, Click on the Group By and group your table based on the Category and Sum of the Weighting:

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
- PowerBiMXM
  Frequent Visitor
  4 years ago
  Hi Vahid,
  
  Thanks for the reply!
  
  I've just followed your steps but unfortunately, it doesn't seem to retain the slicer selections.
  
  I created a sample dashboard to show what I mean but I can't seem to attach it here. Here are some screenshots instead:
  
  I did have a go at creating a measure which sort of works but it doesn't show up against each category, only against each Q ID:
  
  Thanks
  
  P.S. I'm the same person! I've just logged on using my work account!
Anonymous
Not applicable
4 years ago
Hi zcec ,

Here are the steps you can follow：

1. Create table with Enter data.

2. There is no relationship between the newly created table and the other three tables

3. Create measure.

Score = var _select=SELECTEDVALUE('Table1'[Response]) var _tabel1=CALCULATE(SUM('Table1'[Weighting]),FILTER(ALL(Table1),'Table1'[category]=MAX('Category_Table'[Category])&&'Table1'[Response]=_select)) var _table2=CALCULATE(SUM('Table2'[Weighting]),FILTER(ALL(Table2),'Table2'[category]=MAX('Category_Table'[Category])&&'Table2'[Response]=_select)) var _table3=CALCULATE(SUM('Table3'[Weighting]),FILTER(ALL(Table3),'Table3'[category]=MAX('Category_Table'[Category])&&'Table3'[Response]=_select)) return IF( MAX('Category_Table'[Category])="A",_tabel1+_table2,_table3)

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Summing from multiple tables AFTER slicers.pbix38 KB
- PowerBiMXM
  Frequent Visitor
  4 years ago
  Thanks for working on this!
  
  I've just tried it out, though, and it seems to only work for the response given to Q1. I tried tweaking the formula slightly and bringing in a response slicer for each of the questions but selecting a response in one Q filters down the available responses in the remaining questions
PaulDBrown
Community Champion
4 years ago
Append all the tables in Power Query

Optional but recommended:
Create a dimension table for Category. Select new table under modeling and:

Create a measure for the sum of wieghting:

Sum Weight = SUM(Append1[Weighting])

Create a table visual with the field from the dimension table and the measure to get:
- PowerBiMXM
  Frequent Visitor
  4 years ago
  Hi Paul,
  
  Thanks for the reply.
  
  This is similar to Vahid's idea but unfortunately, when I set a filter for Q1 on No, I can't set the filter for Q2 and 3 to Yes.
  - PaulDBrown
    Community Champion
    4 years ago
    Here is a way using the appended tables. You need to create Yes/no Tables for every question and link them to the appended table using inactive relationships. This is how the model looks like:
    
    Create measures for every question as follows:
    
    Sum Weight 1 = CALCULATE(SUM(Append1[Weighting]), USERELATIONSHIP(Append1[Response],'Resonse 1'[Response 1]))
    Sum Weight 2 = CALCULATE(SUM(Append1[Weighting]), USERELATIONSHIP('Append1'[Response],'Resonse 2'[Response 2]))
    
    Set up a slicer using the corresponding Response table and add the measure for each response. Select the tabel visual and set the filter for the corresponding question.
    
    to get
    
    I've attached the sample PBIX file
    Appended Tables.pbix36 KB