Forum Discussion

Bfdc's avatar
Bfdc
Frequent Visitor
4 years ago
Solved

Score depending on answer

Hi All,

Let's try to make myself understood

I've got a list of 7 Risks associated to a consequence parameter.

Each Risk is likely to happen from 1 to 5 - Depneding of the user's input

The score will be the [consequence] times the [likelihood].

So, i wonder what's the best way to one score per risk. Do i need to create a table per risk and 7 different measures? Is it possible to relate both tables and then create like a table including those 7 scores? 

Thank you so much! 

B.

Need Help
  • rohit_singh's avatar
    rohit_singh
    4 years ago

    Hi Bfdc ,

    Please try this :

    1) Create two tables and load them into report view

    Table 1 : Risk 


    Table 2: Likelihood

     

    2) Put the likelihood column into a slicer and the risk columns into a table visual.

    3) Create a measure named "Score" as shown below 

     

    Score =

    var _sel = SELECTEDVALUE(Likelihood[Likelihood])

    var _score =

    SUMX(risk,
    Risk[Consequence] * _sel
    )

    Return
    _score

    For any value of likelihood selected, it will show you scores for all risk no.s

    Kind regards,

    Rohit


    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! 🙂

     

4 Replies

  • rohit_singh's avatar
    rohit_singh
    Solution Sage
    4 years ago

    Hi Bfdc ,

    Please try this :

    1) Create two tables and load them into report view

    Table 1 : Risk 


    Table 2: Likelihood

     

    2) Put the likelihood column into a slicer and the risk columns into a table visual.

    3) Create a measure named "Score" as shown below 

     

    Score =

    var _sel = SELECTEDVALUE(Likelihood[Likelihood])

    var _score =

    SUMX(risk,
    Risk[Consequence] * _sel
    )

    Return
    _score

    For any value of likelihood selected, it will show you scores for all risk no.s

    Kind regards,

    Rohit


    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! 🙂

     

    • Bfdc's avatar
      Bfdc
      Frequent Visitor
      4 years ago

      Thank you so much rohit_singh - I've just learnt few things with that DAX formula!

       

      However, the issue here is that every Risk has a different likelihood, not all them are likely to happen at the same level. So, the end user should be able to say how likely is each of them from 1-5. 
      I wonder if the easiest thing to do is to create 7 different scores and then 7 tables.

      • rohit_singh's avatar
        rohit_singh
        Solution Sage
        4 years ago

        Hi Bfdc ,

        No worries. I wonder if this solution works for you : 

        Modify your table likelihood to include a column for Risk. Each risk value will have different levels of likelihood as shown.


        Create a relationship between the new likelihood table and the risk table.

        Use the likelihood column from the new table as the slicer. 

         

        You will see all risk values that have the selected likelihood.

         

        I don't know how much this will help, but you can think of modifying your table schemas slightly to fit your requirements.

        Kind regards,

        Rohit


        Please mark this answer as the solution if it resolves your issue.
        Appreciate your kudos! 🙂

  • Bfdc's avatar
    Bfdc
    Frequent Visitor
    4 years ago

    Everything is of great help!  

    rohit_singh , I do have the relationship between both folders, but I do need to select one likelihood per risk to filetr the risk applied and make the score work properly.
    So, I've created one table per risk (7 in total), so I could create 7 measurements for the scoring with the DAX you suggested (Thnaks!). I'm sure there is a way to do this more simple with DAX, but just cannot managed

     

     

    That's how it should look like at the end, user selects likelihood per risk. So, that figure times the "consequence" of each risk" will give the score in a radar chart  

     

    If anyone comes up with a more simple way, great!

    Thank you so much! 🙂