Help with Grouping Answers by Question ID

[Rate]

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_id	question_id	question_name	value_number	NPS Literal	User_id
1461	1312	trabajo_etico	1	Detractors	18833
47	1312	trabajo_etico	2	Detractors	18950
3306	1312	trabajo_etico	2	Detractors	19259
541	1312	trabajo_etico	2	Detractors	18840
8	1312	trabajo_etico	5	Promotor	18778
28	1312	trabajo_etico	5	Promotor	18928
33	1312	trabajo_etico	5	Promotor	19214
1523	1313	empresa_direccion_correcta	4	Promotor	19108
1528	1313	empresa_direccion_correcta	4	Promotor	19119
1344	1313	empresa_direccion_correcta	4	Promotor	18867
1461	1313	empresa_direccion_correcta	2	Detractors	18833
958	1313	empresa_direccion_correcta	2	Detractors	19179
8	1313	empresa_direccion_correcta	5	Promotor	18778
23	1313	empresa_direccion_correcta	5	Promotor	18941
1277	1318	empresa_innovadora	3	Passive	19004
1279	1318	empresa_innovadora	3	Passive	18726
8	1320	empresa_mas_cercana_consumidores_que_competidores	4	Promotor	18778
13	1320	empresa_mas_cercana_consumidores_que_competidores	4	Promotor	18881
23	1320	empresa_mas_cercana_consumidores_que_competidores	4	Promotor	18941
244	1320	empresa_mas_cercana_consumidores_que_competidores	4	Promotor	18747
3337	1320	empresa_mas_cercana_consumidores_que_competidores	4	Promotor	19015
3315	1320	empresa_mas_cercana_consumidores_que_competidores	4	Promotor	19017
2916	1320	empresa_mas_cercana_consumidores_que_competidores	4	Promotor	19022
226	1320	empresa_mas_cercana_consumidores_que_competidores	2	Detractors	18693
46	1320	empresa_mas_cercana_consumidores_que_competidores	2	Detractors	18781
341	1320	empresa_mas_cercana_consumidores_que_competidores	2	Detractors	18697
228	1320	empresa_mas_cercana_consumidores_que_competidores	3	Passive	19208
30	1320	empresa_mas_cercana_consumidores_que_competidores	3	Passive	19212
243	1320	empresa_mas_cercana_consumidores_que_competidores	3	Passive	18920
247	1320	empresa_mas_cercana_consumidores_que_competidores	3	Passive	19244
254	1320	empresa_mas_cercana_consumidores_que_competidores	3	Passive	18639

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_id	Age	Company	Area
18833	23	Company A	Finance
18950	24	Company C	Marketing
19259	25	Company B	Sales
18840	26	Company A	Sales
18778	27	Company C	Finance
18928	28	Company B	Marketing
19214	29	Company A	Marketing
18834	30	Company C	Sales
18951	31	Company B	Finance
19260	32	Company A	HR
18841	33	Company C	Management
18779	34	Company B	Management
18929	35	Company A	Management
19215	36	Company C	HR
18835	37	Company B	HR

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 

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!

[MFelix]

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]

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 - ❤️
   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 · Accepted solution]

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]

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,

[MFelix]

Hi @Rate .

You need to create a new column on the Slicer table with the Max and min value for promotors then change your measure to something like this:

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

Should work as you need.

Regards,

MFelix

[Rate]

Again @MFelix , thanks a lot. You are my saviour.

Have a great day.