Forum Discussion
Helper IICalculate between displayed tables
Good morning
I am looking for guidance and or a solution to a probelm I am having and I appreciate any help I can get.
I have 3 tables
TableSubject is displayed on screen and has two columns as below. It has a row of subjects and the count of how many times they appear in a sliced time filter. This is derived from a pivoted table. The count of times gives me a weighting of importance to the user. The weighting will change based on viewer slicer selection.
The second table, TableCampaign, is a table which contains a list of campaigns and the subject(s) that they cover. It has a value under the subjects which determine the relevance of the subject. These values are fixed and will not change.
The third table is my desired result.
TableResult contains a list of the campaigns and a score. The score is derived from adding up all the values of the subjects in a row, multiplied by each weighting. For example Camp1 = Subject 1 * Subject 1 weighting + subject 4 * subject 4 weighting = (50*2)+(1*25) = 125
For TableResult I will sort the score largest to smallest. This then tells me what campaigns are relevent to the current customer experience.
I have recreated the tables below.
While a complete solution would be really nice, I am looking for guidance on how to get the tables to relate so that i can do the calculations and what method i should use to ensure the culatulations refresh on use slicer selection.
I look forward to any guidance you can provide.
Lola
| TableSubject | Weighting |
| Subject1 | 50 |
| Subject2 | 40 |
| Subject3 | 30 |
| Subject4 | 25 |
| Subject5 | 20 |
| Subject6 | 15 |
| Subject7 | 12 |
| Subject8 | 10 |
| Subject9 | 5 |
| Subject10 | 1 |
| TableCampaign | Subject1 | Subject2 | Subject3 | Subject4 | Subject5 | Subject6 | Subject7 | Subject8 | Subject9 | Subject10 |
| Camp1 | 2 | 1 | ||||||||
| Camp2 | 2 | 1 | ||||||||
| Camp3 | 2 | 2 | ||||||||
| Camp4 | 1 | 2 | ||||||||
| Camp5 | 2 | 1 | 1 | |||||||
| Camp6 | 1 | 2 | 1 | |||||||
| Camp7 | 2 | 1 | 1 | |||||||
| Camp8 | 1 | 2 | ||||||||
| Camp9 | 1 | 2 | 1 | |||||||
| Camp10 | 1 | 1 | 1 | 2 |
| TableResult | Score |
| Camp1 | 125 |
| Camp2 | 85 |
| Camp3 | 100 |
| Camp4 | 80 |
| Camp5 | 73 |
| Camp6 | 80 |
| Camp7 | 135 |
| Camp8 | 35 |
| Camp9 | 80 |
| Camp10 | 105 |
5 Replies
- MFelix
Super User
Hi MyWeeLola ,
For this you need to do the following:
- Unpivot the campaing table so that you have the following setup
- Do a relationship between the subject and the campaing:
- Add the following measure:
Weighted Value = SUMX( ADDCOLUMNS( 'Campaign', "Weigt", RELATED(Subject[Weighting]) ), 'Campaign'[Value] * [Weigt] )Check PBIX file attach.
MyWeeLolasomeone accepted this as the solution, but it did not fix the original problem. I went to an external resource to fix it as nothing anyone posted here resolved the issue. However i am appreciative of people trying to help.
- MFelix
Hi MyWeeLola ,
What is the problem, I apologize for missintreperting the question. Can you please elaborate on what is the question at hand.
To what I can understand you want to have the calculation based on the weighted values, and the solution I gave you does that calculation.
Is there any issue with the unpivot of the tables? What is not matching your needs?
I see that the last values are not macthing the result you shared but I believe is because I copied the information incorrectly for line 10.
I have also unmarked the answer to be correct since you refer that is not the solution (other user on the community can accept answers and probably that is what happened).
