Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there!
I am building a model to try to compare data within the same table according to two different combinations of slicers affecting the same columns.
My data (a simplified version) is structured in two tables, one by containing the projects and its characteristics, and another one with the answers to each project:
Table 1
Project | Country | Industry |
Pro_1 | Spain | Automotive |
Pro_2 | Australia | Automotive |
Pro_3 | Brazil | Forestry |
Pro_4 | UK | Coal mining |
Pro_5 | UK | Plastics |
Pro_6 | US | Machinery |
Pro_7 | Spain | Coal mining |
Pro_8 | Uganda | Plastics |
Pro_9 | Mexico | Machinery |
Pro_10 | US | Automotive |
Table 2
Project | Answer | Score |
Pro_1 | 1 | -2 |
Pro_1 | 2 | 1 |
Pro_1 | 3 | 2 |
Pro_1 | 4 | 0 |
Pro_1 | 5 | 2 |
Pro_1 | 6 | 1 |
Pro_2 | 1 | -1 |
Pro_2 | 2 | 2 |
Pro_2 | 3 | -2 |
Pro_2 | 4 | 0 |
Pro_2 | 5 | 1 |
Pro_2 | 6 | 2 |
Pro_3 | 1 | 2 |
Pro_3 | 2 | 1 |
Pro_3 | 3 | 2 |
Pro_3 | 4 | 1 |
Pro_3 | 5 | -1 |
Pro_3 | 6 | -2 |
Pro_4 | 1 | 2 |
Pro_4 | 2 | 2 |
Pro_4 | 3 | 1 |
Pro_4 | 4 | 2 |
Pro_4 | 5 | -2 |
Pro_4 | 6 | -2 |
Pro_5 | 1 | -2 |
Pro_5 | 2 | 1 |
Pro_5 | 3 | 2 |
Pro_5 | 4 | 0 |
Pro_5 | 5 | 2 |
Pro_5 | 6 | 1 |
Pro_6 | 1 | -1 |
Pro_6 | 2 | 2 |
Pro_6 | 3 | -2 |
Pro_6 | 4 | 0 |
Pro_6 | 5 | 1 |
Pro_6 | 6 | 2 |
Pro_7 | 1 | 2 |
Pro_7 | 2 | 1 |
Pro_7 | 3 | 2 |
Pro_7 | 4 | 1 |
Pro_7 | 5 | -1 |
Pro_7 | 6 | -2 |
Pro_8 | 1 | 2 |
Pro_8 | 2 | 2 |
Pro_8 | 3 | 1 |
Pro_8 | 4 | 2 |
Pro_8 | 5 | -2 |
Pro_8 | 6 | -2 |
My goal is to compare the average of scores across questions and for specific questions for one specific project, which would be selected using a slicer with that purpose (e.g: Pro_1) with the same metrics of a benchmark defined by selecting a mix of project characteristics (by using slicers) (e.g: projects in machinery). So far I have been able to develop a measure calculating the desired metric for specific projects / project characteristics and also measures to compare the two. The measures would be as follows:
Project Score = calculate( average( Table_2[Score]), containsrow({"Pro_1"},Table_1[Project])) Benchmark Score = calculate( average( Table_2[Score]), containsrow({"Machinery"},Table_1[Industry])) Project to Benchmark = Project Score - Benchmark Score
However, the problem is that those measures are not dynamic, and you need to change the measures every time you want to choose a new project or benchmark. I cannot find a way to make the content of containsrow dynamic defined by slicers.
Do you know any way to do this / workarounds using other systems to get to the desired outcome?
Thank you in advance for your help!
Solved! Go to Solution.
Hi @nogarr
If i understand you correctly,
e.g:
there are two slicers-
Project->Pro_1, Industry->machinery
Project Score->scores of Pro_1
Benchmark Score->scores of machinery
If so,
First create two table
industry = VALUES(Table_1[Industry]) project table = VALUES(Table_1[Project])
i create measures using "sum", you can create your measures by replacing "sum" with "average"
Project Score = calculate( SUM(Table_2[Score]),FILTER(Table_2,Table_2[Project]=SELECTEDVALUE('project table'[Project]))) Benchmark Score = calculate(SUM(Table_2[Score]),FILTER(Table_1,Table_1[Industry]=SELECTEDVALUE(industry[Industry])))
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @nogarr
If i understand you correctly,
e.g:
there are two slicers-
Project->Pro_1, Industry->machinery
Project Score->scores of Pro_1
Benchmark Score->scores of machinery
If so,
First create two table
industry = VALUES(Table_1[Industry]) project table = VALUES(Table_1[Project])
i create measures using "sum", you can create your measures by replacing "sum" with "average"
Project Score = calculate( SUM(Table_2[Score]),FILTER(Table_2,Table_2[Project]=SELECTEDVALUE('project table'[Project]))) Benchmark Score = calculate(SUM(Table_2[Score]),FILTER(Table_1,Table_1[Industry]=SELECTEDVALUE(industry[Industry])))
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, that did help!
Hi, @Washivale, unfortunately I have tried that before, but it does not work when you want to do a filter combining several options (e.g: filter in several industries / projects).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |