Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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:
Sport | TEXT | Sentiment |
Hockey | Text 1 | Positive |
Hockey | Text 2 | Positive |
Hockey | Text 3 | Negative |
Hockey | Text 4 | Positive |
Hockey | Text 5 | Negative |
Hockey | Text 6 | Positive |
Hockey | Text 7 | Positive |
Golf | Text 8 | Positive |
Golf | Text 9 | Positive |
This is a an example of the type of chart i'd like to create from the data:
Solved! Go to 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.
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:
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?
.@parry2k - Any suggestions for when there is a five-point scale vs a two-point one?
@Anonymous 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.
@Anonymous 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]
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...
@jvirgi is this what you are looking for?
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.
@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.
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.
User | Count |
---|---|
112 | |
94 | |
87 | |
75 | |
65 |
User | Count |
---|---|
138 | |
112 | |
109 | |
98 | |
93 |