Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
Following some excellent advice by @parry2k from this forum (here and here), I created a divergent stacked bar chart for a 5-point Likert scale. To sort the responses as shown, I created a measure for each. To show the neutrals either across the centre or at the ends of each side, I created a measure "Neutral-" and a measure "Neutral+".
My issue:
I am planning to create these kinds of visuals for many questions and creating nine visuals for each does not seem practical. Also, this may reduce my (and other users' if I published it on a dashboard) ability to analyse the data inside Power BI. Further, the legend is useless as it shows the values in a different order and, of course, shows the two neutrals separately.
My questions:
Is there a more efficient way of doing this, or at least is there a way of automating the creation of these measures so that I do not have to create them step-by-step for each question of the survey again?
I would appreciate your ideas (in simple language, please - I consider myself proficient in Excel but have less than 48 hours of experience in Power BI... ).
Below are the visual, the measures and the test data I used (I know it should be 'canoe' rather than 'canoo', sorry).
My visual:
My Measures (kudos to @parry2k😞
Base Count = COUNTROWS ( 'Sports' )
Sentiment Count = IF(SELECTEDVALUE(Sports[Sentiment]) = "Strongly agree" || SELECTEDVALUE('Sports'[Sentiment]) = "Agree",1,-1)* [Base Count]
% Sentiment = DIVIDE ( [Sentiment Count], CALCULATE ( [Base Count], ALLSELECTED( Sports[Sentiment] ) ) )
Neutral+ = DIVIDE( (COUNTAX(FILTER('Sports',[Sentiment]="Neutral"),[Sentiment])),2)
Neutral- = DIVIDE( (COUNTAX(FILTER('Sports',[Sentiment]="Neutral"),[Sentiment])),-2)
Agree = COUNTAX(FILTER('Sports',[Sentiment]="Agree"),[Sentiment])
Strongly agree = COUNTAX(FILTER('Sports',[Sentiment]="Strongly agree"),[Sentiment])
Disagree = COUNTAX(FILTER('Sports',[Sentiment]="Disagree"),[Sentiment]) * -1
Strongly disagree = COUNTAX(FILTER('Sports',[Sentiment]="Strongly disagree"),[Sentiment]) * -1
My data:
Sport | Sentiment |
Hockey | Agree |
Hockey | Agree |
Hockey | Agree |
Hockey | Agree |
Hockey | Strongly agree |
Hockey | Strongly agree |
Hockey | Strongly agree |
Hockey | Strongly agree |
Hockey | Strongly agree |
Hockey | Strongly agree |
Hockey | Strongly agree |
Hockey | Neutral |
Hockey | Neutral |
Hockey | Neutral |
Hockey | Neutral |
Hockey | Neutral |
Hockey | Disagree |
Hockey | Disagree |
Hockey | Strongly disagree |
Hockey | Strongly disagree |
Golf | Strongly disagree |
Golf | Strongly disagree |
Golf | Strongly disagree |
Golf | Strongly disagree |
Golf | Disagree |
Golf | Disagree |
Golf | Disagree |
Golf | Disagree |
Golf | Disagree |
Golf | Disagree |
Golf | Disagree |
Golf | Neutral |
Golf | Neutral |
Golf | Neutral |
Golf | Agree |
Golf | Agree |
Golf | Agree |
Golf | Strongly agree |
Golf | Strongly agree |
Golf | Strongly agree |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
Canoo | Neutral |
I would transform/append your data to add a question column to the data, so you can use the existing set of measures for everything. Also, why did you need a measure for each response? Seems like you could use CALCULATE with ALL to calculate the pct of total for each response.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |