Forum Discussion

jvirgi's avatar
jvirgi
Icon for Helper III rankHelper III
6 years ago
Solved

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: Sport TEXT Se...
Need Help
Tips and Tricks
  • parry2k's avatar
    parry2k
    6 years ago

    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

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.

jvirgi's avatar
jvirgi
Icon for Helper III rankHelper III
6 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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? 

    • jvirgi's avatar
      jvirgi
      Icon for Helper III rankHelper III
      6 years ago

      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]

       

      • matan_benyishay's avatar
        matan_benyishay
        Icon for Helper II rankHelper II
        3 years ago

        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...

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

    • jvirgi's avatar
      jvirgi
      Icon for Helper III rankHelper III
      6 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.