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
jvirgi
Helper III
Helper III

Creating a diverging stacked bar chart to show sentiment

I was trying to create a diverging stacked bar chart to show the %'s of sentiment on data.  I can't figure out an easy way to do this in power BI.  

Here is a sample set of data:

SportTEXTSentiment
HockeyText 1Positive
HockeyText 2Positive
HockeyText 3Negative
HockeyText 4Positive
HockeyText 5Negative
HockeyText 6Positive
HockeyText 7Positive
GolfText 8Positive
GolfText 9Positive

 

This is a an example of the type of chart i'd like to create from the data:

chart-example.png

 
1 ACCEPTED SOLUTION

@jvirgi add following 3 measure, of course, these can be done in one measure but I like to break up the measure for easy debugging and maintenance.

 

Would appreciate Kudos 🙂 if my solution helped.

 

Base Count = COUNTROWS ( 'Table' )

Sentiment Count = IF ( SELECTEDVALUE( 'Table'[Sentiment] ) = "Negative", -1, 1 ) * [Base Count]

% Sentiment = DIVIDE ( [Sentiment Count], CALCULATE ( [Base Count], ALLSELECTED( 'Table'[Sentiment] ) ) )

 

To visualize do the following and on format pane, you can change the colors

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

18 REPLIES 18
matan_benyishay
Helper II
Helper II

Hi,

I've been trying to work through a similar viz using 100% stacked bars. I've got it mostly working, except that the negative values are displayed out of order.

Workflow:

  • Already coded responses to -2 (strongly disagree) to 2 (strongly agree); neutral is 0
  • Created a -1/+1 helper column: 1 if > 0, -1 if <= 0 (I want neutrals on the "negative" side for this purpose)
  • Take sum of -1/+1 helper column
  • Sort responses based on value

The problem is that for whatever reason, the negative values show in order of increasing count size, not response size: neutral, disagree, strongly disagree. The positive values are correct: agree, strongly agree. I've actually gotten the legend to sort correctly by using a "sort by" column, but not the actual chart. It's hard to explain verbally; I'm attaching the screenshot. 

I tried checking the "reverse stacks" setting under "bars". That makes the negative values correct, but then also reverses the positive values...

 

Any suggestions?

PowerBI diverging stacked bars.png

 

dpowellLE
Frequent Visitor

.@parry2k - Any suggestions for when there is a five-point scale vs a two-point one?

@dpowellLE Do you have an example of how you'd want it to look?  I guess you'd want some of the 5 going on the negative axis and some on the positive side?

I would like it to look similar to this visualization. The challenge I am facing with the proposed solution is that the SELECTEDVALUE operator for the Sentiment count only works with binary responses. I am trying to figure out how to modify that step as I have now created a conditional column to range from -2 to 2.

@dpowellLE Did you ever find a solution for this? I'm working against a very similar challenge. Any advice on how you've displayed neutrals would be much appreciated.

What if you just use an OR statement to label the 2 negative aspects with the -1; the 2 positive ones would automatically get a 1; youd have to decide how you want to handle the neutrals if you show or not.  

 

Since the legend is used to stack your data by the 5 different responses, it would show them all.  you'd have to think if you display the neutrals or not.

This is so helpful! I would like to show neutrals. How could I go about that?

I have the neutrals (neither agree / disagree) being shown on the positive side of the axis in the example so they are in there colored gray. 

 

If you wanted the neutrals shown on the negative side, you'd add another OR statement in that sentiment count formula.

Thanks for noting that! I am going to look for a solution to anchor the neutrals to display evenly across negative and positive sides of the axis if possible.

Let me know if you find a way to do it.  

 

The only way i'm thinking about is splitting your Neutrals into 2 buckets,  a positive side and a negative side.   Then you'd basically take the percentage of neutrals and divide by 2 for each of those buckets.  

It sounds like giving both negative values -1 and the positive values a 1 would lead to the same example in the stated solution without distinguishing them but I may be mistaken. Can you demonstrate with an example? 

The -1 and 1 are used as a means of counting the number within each bucket, and then the % sentiment divides by the total #.  so the -1 just assigns it to the negative side of the axis.  

I'm not sure how you'd center the neutrals on the axis, but heres an example pbix:

 

I just changed the sentiment Count formula to use the OR statement:

Sentiment Count = IF(SELECTEDVALUE('test data'[Agreement]) = "strongly disagree" || SELECTEDVALUE('test data'[Agreement]) = "somewhat disagree",-1,1)* [Base Count]

 

Annotation 2020-04-28 115125.png

Hi! I added a more specific workflow further up in the thread, but how did you get the strongly disagree > disagree > neutral to be in the right order on the negative part of the axis? Mine were reversed, even though the positives were in the right order...

parry2k
Super User
Super User

@jvirgi is this what you are looking for?

 

image.png



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k
Yes! That’s exactly what I’m looking to do. How should I do it?

@jvirgi add following 3 measure, of course, these can be done in one measure but I like to break up the measure for easy debugging and maintenance.

 

Would appreciate Kudos 🙂 if my solution helped.

 

Base Count = COUNTROWS ( 'Table' )

Sentiment Count = IF ( SELECTEDVALUE( 'Table'[Sentiment] ) = "Negative", -1, 1 ) * [Base Count]

% Sentiment = DIVIDE ( [Sentiment Count], CALCULATE ( [Base Count], ALLSELECTED( 'Table'[Sentiment] ) ) )

 

To visualize do the following and on format pane, you can change the colors

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k How did you setup the chart on the format pane?  Looked like you were going to post a picture?  Thanks! 

@jvirgi oops, sorry about that, here it is. working on gazillion things at the same time.  Would appreciate Kudos 🙂 if my solution helped.

 

image.png



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.