Forum Discussion

Rate's avatar
Rate
Helper III
7 years ago
Solved

Help with Grouping Answers by Question ID

Hello all!    I am having trouble with a calculation for a big survey we did in our Company. Could you please give help me this?   I have the following Tables: 1. Responses Lines - 1 line per an...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Rate ,

     

    First of all sorry for the delay, a hard week at work.

     

    Create the following table to use on your slicer:

     

    Type Promotros Detractors
    Strenght 0,75 0,15
    Weaknessess 0,5 0,25

     

    the values on the Promotors and detractors will serve as bounderies that you want to set on your slicer

     

    Create the following measures:

     

    Passive =
CALCULATE (
    COUNT ( Responses[question_id] );
    Responses[NPS Literal] = "Passive"
)
    / CALCULATE ( COUNT ( Responses[question_id] ) )


Detractor=
CALCULATE (
    COUNT ( Responses[question_id] );
    Responses[NPS Literal] = "Detractors"
)
    / CALCULATE ( COUNT ( Responses[question_id] ) )


Promotor=
CALCULATE (
    COUNT ( Responses[question_id] );
    Responses[NPS Literal] = "Promotor"
)
    / CALCULATE ( COUNT ( Responses[question_id] ) )


Filter =
SWITCH (
    TRUE ();
    SELECTEDVALUE ( Rules[Type] ) = "Strenght"; IF (
        [Promotor] > MIN ( Rules[Promotors] )
            && [Detractor] < MIN ( Rules[Detractors] );
        1;
        0
    );
    SELECTEDVALUE ( Rules[Type] ) = "Weaknessess"; IF (
        [Promotor] < MIN ( Rules[Promotors] )
            && [Detractor] > MIN ( Rules[Detractors] );
        1;
        0
    );
    1
)

     

    Create a 100% Stacked bar chart and place the measures Detractor, Passive and Promotor on the values and question name on the x-axis. On the Visual filter place the measure Filter and choose the option is 1:

     

    This should give the expected result, see attach PBIX file.

     

     

    Regards,

    MFelix

MFelix's avatar
MFelix
Super User
7 years ago

Hi Rate ,

 

In order to help you better can you please help  me understandar better your model.

 

  • How are you calculating the clara_correlation and the criticar_decisiones_mi_area?
  • Wher do you get the Desfavorable, Neutral and Favorable categories?

Can you please give the calculations you are making based on the data you show.

 

Regards,

MFelix

 

 

  • Rate's avatar
    Rate
    Helper III
    7 years ago

    Hello MFelix 

     

    Thanks a lot for your interest. 

     

    1. Clara_correlation and criticar_decisiones are two examples of questions, as those included in the example table.
      1. The calculation is simple: I am using a 100% stacked bar chart, with the value being the number of answers for each question and the legend the category defined before (Detractor, Passive or Promotor).
    2. Sorry for this. I used the Spanish image and translated into English for the example Tables. These categories are the same as mentioned before.
      1. Desfavorable = Detractor - <3
      2. Neutral = Passive - = 3
      3. Favorable = Promotor - >= 4

    At the end, what I am trying to achieve is to include a slicer that could hide/show the questions that meet the criteria exposed before:

    - Strenghts: >75% of answers belong to Promotors and less that 15% of answers belong to Detractors
    - Weaknessess: <50% of answers belong to Promotors and more than 25% of answers belong to Detractors 

     

    So, in this new example (attached below), when I choose to filter by Strenghts, I want the graph to hide "Movilidad Geográfica", while, at the same time, being able to use all the Users information in Users Table (age...)

     

    Please, do let me know if you need any further clarification!

     

    Thanks a lot!

    • MFelix's avatar
      MFelix
      Super User
      7 years ago

      Hi Rate ,

       

      First of all sorry for the delay, a hard week at work.

       

      Create the following table to use on your slicer:

       

      Type Promotros Detractors
      Strenght 0,75 0,15
      Weaknessess 0,5 0,25

       

      the values on the Promotors and detractors will serve as bounderies that you want to set on your slicer

       

      Create the following measures:

       

      Passive =
CALCULATE (
    COUNT ( Responses[question_id] );
    Responses[NPS Literal] = "Passive"
)
    / CALCULATE ( COUNT ( Responses[question_id] ) )


Detractor=
CALCULATE (
    COUNT ( Responses[question_id] );
    Responses[NPS Literal] = "Detractors"
)
    / CALCULATE ( COUNT ( Responses[question_id] ) )


Promotor=
CALCULATE (
    COUNT ( Responses[question_id] );
    Responses[NPS Literal] = "Promotor"
)
    / CALCULATE ( COUNT ( Responses[question_id] ) )


Filter =
SWITCH (
    TRUE ();
    SELECTEDVALUE ( Rules[Type] ) = "Strenght"; IF (
        [Promotor] > MIN ( Rules[Promotors] )
            && [Detractor] < MIN ( Rules[Detractors] );
        1;
        0
    );
    SELECTEDVALUE ( Rules[Type] ) = "Weaknessess"; IF (
        [Promotor] < MIN ( Rules[Promotors] )
            && [Detractor] > MIN ( Rules[Detractors] );
        1;
        0
    );
    1
)

       

      Create a 100% Stacked bar chart and place the measures Detractor, Passive and Promotor on the values and question name on the x-axis. On the Visual filter place the measure Filter and choose the option is 1:

       

      This should give the expected result, see attach PBIX file.

       

       

      Regards,

      MFelix

      • Rate's avatar
        Rate
        Helper III
        7 years ago

        Hello MFelix 

         

        I have no words to express my inmense gratification! This just saved my whole project. Thank you so so much. It worked like a charm.

         

        As a bonus, in case you have the time, I have a third boundary that I didn't include in the first request: "Opportunities", with the following restrictions:

        Promotors >= 0,5 && Promotors < 0,7

        Detractors < 0,15

         

        Do you know how to incorporate this third classification into your solution?

         

        Again, thank you soo much. I think I love you.

         

        Regards,