cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jamesb1013 Frequent Visitor
Frequent Visitor

Simple matrix to bar chart does not work properly

Hello!

 

I am trying to place a simple set of matrix data that is grouped by region and sorted by total sales into a bar chart.  The data is correctly shown below.

 

                             PowerBI-MatrixData.png

Now the problem occurs when selecting the bar chart (stacked column chart or clustered column chart).  Once this occurs, the data is no longer grouped properly and the chart is not correct.

 

                             PowerBI-MatrixBarChart.png

Ultimately, the chart should look something like this that was accomplished in Tableau (formatting aside).  However, we don't want to use Tableau, we want to use Power BI.  

 

                            Tableau-BarChart.png

Is the ability to do this not available in the May 2018 release of Power BI, or is there more configuration that has to be done that is not immediately apparent?  I've tried everything that I can think of and formatting a simple table is just not easy and it should be.  Even sorting the chart by Total Sales doesn't provide the correct solution as the Regions do not remain grouped as shown above with Tableau. 

 

                            PowerBI-MatrixBarChart2.png

Does anyone have any ideas as how to get where I need to go with Power BI instead of Tableau?  Thank you very much for your help!!

 

James

6 REPLIES 6
shebr Member
Member

Re: Simple matrix to bar chart does not work properly

Hi @jamesb1013

 

Lets make you a power bi convert. I've done this with a similar dataset, see image below, and the steps to do it are below also.

 

So what you need to do here is:

 

1. As you've done, take a copy of your matrix and use the 'Stacked Column Chart' visual.

2. Organise your columns so that they both sit on above each other on the x-axis area. See image.

3. On the graph, select the icon that drills down all the data down to the bottom levels, again see image.

4. On the options section for the graph (the paintbrush icon), under x-axis, there's a switch that says 'Concatenate columns'. Switch that to off. See image.

 

This should then get your data split up as you want. Use the data colours to colour them in as necessary.

 

Let me know how you get on,

 

Thanks

 

shebr

 

grouping1.JPGgrouping2.JPGgrouping3.JPG

jamesb1013 Frequent Visitor
Frequent Visitor

Re: Simple matrix to bar chart does not work properly

@shebr,

 

Thank you for the quick response!  I have attempted to do as you have suggested but could not make it work but I'm sure it is something on my end.  Your directions were very clear so I'll provide my steps below as I interpreted them from your email.

 

I start by adding a new matrix to the desktop.

 

          PBID-Step1.png

 

After that, I drag Region and Rep into the Axis area followed by Total Sales into the Value area.

 

          PBID-Step2.png

 

Next, I clicked on the double arrow button (Show next level), until I reached the lowest level to obtain the same view as you had in your screenshot with the subtotals.

 

          PBID-Step3.png

 

I then proceeded to sort the Total Sales column in decending order by clicking the small arrow beside Total Sales.  This provides the correct grouping and order that the bar chart needs to follow.

 

          PBID-Step4.png

 

I have confirmed that the Region and Rep fields are on top of each other as noted in your second step.  I then proceeded to click on the Stacked Column Chart icon to obtain the base bar chart.  For visual reference, I also clicked on the See Data icon on the ribbon bar under Visual Tools > Data/Drill.  On the upper left hand corner of the graph I drilled through the data (Expand next level) to get where we are below so as to closely mirror your image.

 

          PBID-Step5.png

 

The last thing I did was went to the options, under the x-axis and turned off 'Concatenate columns', however, I didn't see that it did anything visible.

 

          PBID-Step6.png

 

At this point, is where I believe is the disconnect.  You mentioned at this point I could go into Data Colors and change the colors as I want, and I can do that but it is a lot of work to do especially if I have quite a few more columns.  I think the better way is to just drop in Region in the Legend area and change the colors that way.  However, we still have a couple of issues.

 

          PBID-Step8.png

 

(I've dropped in the Region into the Legend area.) 

 

So, in order to get rid of the Region name on the X-axis so as not to clutter the name of the Rep, I went back up to the upper left hand corner and drilled to the top level and then selected the double arrow button (Show next level) until I reached the bottom.  It didn't change the chart layout other than to remove the Region name, so that problem is solved, but the Regions are still dispersed and not grouped and properly sorted as in the original matrix.  

 

          port09.png

 

Our end goal is to reach something similar, if not exact, to the original Tableau bar chart below, and we're nearly there.  What elementary thing(s) am I missing that is preventing success?  Thank you again @shebr for your help!  -James

 

         PBID-Step9.png

NickNg278 Senior Member
Senior Member

Re: Simple matrix to bar chart does not work properly

@jamesb1013,

Can you click on the ellipsis and select Sort by Region instead of Sort by Total Sales?

jamesb1013 Frequent Visitor
Frequent Visitor

Re: Simple matrix to bar chart does not work properly

@NickNg278,

 

Hello!  Unfortunately that is not an option.  I can either sort by Rep or Total Sales.  I would be nice to be able to just simply group the Regions and then execute a descending sort within each Region.   Thank you for the response!

 

Image.png

shebr Member
Member

Re: Simple matrix to bar chart does not work properly

Hi @jamesb1013

 

At your step 5 image, there is an icon on your graph in the top left corner that represents 'drill up' If you drill up to the top level, then use the 3rd icon in the top left to drill to the bottom of all levels that should group them up as you want.

 

Toggle the concatenate on and off if it does not work immediately.

 

Let me know how you get on. If not, send me over your pbix and i can have a look.

 

Thanks

 

shebr

 

 

jamesb1013 Frequent Visitor
Frequent Visitor

Re: Simple matrix to bar chart does not work properly

Shebr, 

 

Did you have any luck with the two files I sent you?  I tried again and I still can't get things to work with PBI May 2018 edition.  I'm not sure PBI can do it, yet.  Maybe in a future release.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors