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 answer to one question, as shown in the example. The column NPS Literal is calculated based on the value of the answer: 

If >= 4; Promotor

If < 3: Detractor

If 3: Passive

 

surveys_response_idquestion_idquestion_namevalue_numberNPS LiteralUser_id
14611312trabajo_etico1Detractors18833
471312trabajo_etico2Detractors18950
33061312trabajo_etico2Detractors19259
5411312trabajo_etico2Detractors18840
81312trabajo_etico5Promotor18778
281312trabajo_etico5Promotor18928
331312trabajo_etico5Promotor19214
15231313empresa_direccion_correcta4Promotor19108
15281313empresa_direccion_correcta4Promotor19119
13441313empresa_direccion_correcta4Promotor18867
14611313empresa_direccion_correcta2Detractors18833
9581313empresa_direccion_correcta2Detractors19179
81313empresa_direccion_correcta5Promotor18778
231313empresa_direccion_correcta5Promotor18941
12771318empresa_innovadora3Passive19004
12791318empresa_innovadora3Passive18726
81320empresa_mas_cercana_consumidores_que_competidores4Promotor18778
131320empresa_mas_cercana_consumidores_que_competidores4Promotor18881
231320empresa_mas_cercana_consumidores_que_competidores4Promotor18941
2441320empresa_mas_cercana_consumidores_que_competidores4Promotor18747
33371320empresa_mas_cercana_consumidores_que_competidores4Promotor19015
33151320empresa_mas_cercana_consumidores_que_competidores4Promotor19017
29161320empresa_mas_cercana_consumidores_que_competidores4Promotor19022
2261320empresa_mas_cercana_consumidores_que_competidores2Detractors18693
461320empresa_mas_cercana_consumidores_que_competidores2Detractors18781
3411320empresa_mas_cercana_consumidores_que_competidores2Detractors18697
2281320empresa_mas_cercana_consumidores_que_competidores3Passive19208
301320empresa_mas_cercana_consumidores_que_competidores3Passive19212
2431320empresa_mas_cercana_consumidores_que_competidores3Passive18920
2471320empresa_mas_cercana_consumidores_que_competidores3Passive19244
2541320empresa_mas_cercana_consumidores_que_competidores3Passive18639

 

2. User Information - 1 line per person that answered the Survey, with information on their age, role in the Company... with a unique id that connects to the response lines table.

User_idAgeCompanyArea
1883323Company AFinance
1895024Company CMarketing
1925925Company BSales
1884026Company ASales
1877827Company CFinance
1892828Company BMarketing
1921429Company AMarketing
1883430Company CSales
1895131Company BFinance
1926032Company AHR
1884133Company CManagement
1877934Company BManagement
1892935Company AManagement
1921536Company CHR
1883537Company BHR

 

I am trying to calculate a graph as shown, where I get to see if, each specific question has:

- 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 

Desired Graph

 

I tried generating a new Summarize table with one line per question_id, but the problem I am facing is when I try to use slicers (based on the User Information table), that the filtering is done wrong.

 

Do you have any recommendations on how to get the desired graph and still get to filter by the attributes of the User Information table?

 

Again, thanks a lot!

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

6 Replies

  • 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