Summing from multiple tables AFTER slicers

[zcec]

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

[PaulDBrown]

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]

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 · Accepted solution]

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

[PowerBiMXM]

Hi Paul,

Thank you! This is exactly what I need!!

Can't thank you enough!

[PaulDBrown]

You can create yes/no tables to use as extra slicers and measures

[Anonymous]

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

[PowerBiMXM]

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

[VahidDM]

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]

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!