Forum Discussion
Help with Grouping Answers by Question ID
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
Hello MFelix
Thanks a lot for your interest.
- Clara_correlation and criticar_decisiones are two examples of questions, as those included in the example table.
- 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).
- 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!
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
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,
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