Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Andrew28199
Regular Visitor

Divergent stacked bar chart for 5-point Likert scale showing neutrals on both sides

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:

 

Divergent stacked bar chartDivergent stacked bar chart

 

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: 

SportSentiment
HockeyAgree
HockeyAgree
HockeyAgree
HockeyAgree
HockeyStrongly agree
HockeyStrongly agree
HockeyStrongly agree
HockeyStrongly agree
HockeyStrongly agree
HockeyStrongly agree
HockeyStrongly agree
HockeyNeutral
HockeyNeutral
HockeyNeutral
HockeyNeutral
HockeyNeutral
HockeyDisagree
HockeyDisagree
HockeyStrongly disagree
HockeyStrongly disagree
GolfStrongly disagree
GolfStrongly disagree
GolfStrongly disagree
GolfStrongly disagree
GolfDisagree
GolfDisagree
GolfDisagree
GolfDisagree
GolfDisagree
GolfDisagree
GolfDisagree
GolfNeutral
GolfNeutral
GolfNeutral
GolfAgree
GolfAgree
GolfAgree
GolfStrongly agree
GolfStrongly agree
GolfStrongly agree
CanooNeutral
CanooNeutral
CanooNeutral
CanooNeutral
CanooNeutral
CanooNeutral
CanooNeutral
CanooNeutral
CanooNeutral
CanooNeutral
CanooNeutral
CanooNeutral
CanooNeutral
CanooNeutral
CanooNeutral
CanooNeutral
CanooNeutral
CanooNeutral
CanooNeutral
CanooNeutral
1 REPLY 1
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.