Forum Discussion

nogarr's avatar
nogarr
Frequent Visitor
7 years ago
Solved

Measure based on two slicers over the same column

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

ProjectCountryIndustry
Pro_1SpainAutomotive
Pro_2AustraliaAutomotive
Pro_3BrazilForestry
Pro_4UKCoal mining
Pro_5UKPlastics
Pro_6USMachinery
Pro_7SpainCoal mining
Pro_8UgandaPlastics
Pro_9MexicoMachinery
Pro_10USAutomotive

 

Table 2

ProjectAnswerScore
Pro_11-2
Pro_121
Pro_132
Pro_140
Pro_152
Pro_161
Pro_21-1
Pro_222
Pro_23-2
Pro_240
Pro_251
Pro_262
Pro_312
Pro_321
Pro_332
Pro_341
Pro_35-1
Pro_36-2
Pro_412
Pro_422
Pro_431
Pro_442
Pro_45-2
Pro_46-2
Pro_51-2
Pro_521
Pro_532
Pro_540
Pro_552
Pro_561
Pro_61-1
Pro_622
Pro_63-2
Pro_640
Pro_651
Pro_662
Pro_712
Pro_721
Pro_732
Pro_741
Pro_75-1
Pro_76-2
Pro_812
Pro_822
Pro_831
Pro_842
Pro_85-2
Pro_86-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!

 

  • 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.

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support

    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.

    • nogarr's avatar
      nogarr
      Frequent Visitor

      Thank you, that did help!

    • nogarr's avatar
      nogarr
      Frequent Visitor

      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).