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

100 % Stacked bar chart

Dear All,

 

I want to come up with a 100% stacked bar chart view as in the attached screenshot.

Can anybody suggest what is the most efficient way of doing it?

 

2020-01-20_16h33_57.png

 

Data structure provided on the left of the visual.

 

Thanks for any help in this topic.

 

Kind Regards,

Adarsh

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: 100 % Stacked bar chart

Hi @ahumne25 ,

 

Do you have any specific column that you use as ID? If yes I would split your model in two table one with all the Portfolio information that you will not unpivot and delete all the columns A to I and a second table that would be the ID column and the unpivot of the A to I columns then linked both tables by the ID column.

 

To calculate the status you would use the 2nd table and for the rest of the setup the other table.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

8 REPLIES 8
Highlighted
Super User III
Super User III

Re: 100 % Stacked bar chart

Hi @ahumne25 ,

 

On the query Editor select columns A to I and unpivoted then you will get two columns with attribute (A to J) and Value (status)

 

Now create a 100% stacked bar with the following layout:

  • Axis - Attribute column
  • Legend - Value Column
  • Value - Count of attribute.

Should give expected result.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Community Support
Community Support

Re: 100 % Stacked bar chart

Hi @ahumne25 , 

You could refer to above suggestions, choose all columns in Edit Queries->unpivot columns, then use meausre to get it or use M code to get count. You could refer to my sample for details.

 

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Highlighted
Frequent Visitor

Re: 100 % Stacked bar chart

Thanks a lot @MFelix  for this elegant solution.

I have follow up question to this solution. My database has couple of other columns, and when I unpivot these selected columns within the table a lot of extra rows are created. Also I wanted to filter based on the column in the sheet? How can I do that?

Highlighted
Frequent Visitor

Re: 100 % Stacked bar chart

Dear @dax, thanks a lot for your additional input. But what could I do in cases where there are additional columns and I have to use filter?

Highlighted
Super User III
Super User III

Re: 100 % Stacked bar chart

Hi @ahumne25 ,

 

Making the unpivot of columns what you do is to create lines instead of columns, so this will increase the number of rows and the data from the other columns that are not unpivot will be duplicated as many time as columns you have. Be aware that depending on the type of information you have associated with your data you may need to have measures or calculation done in a different way so that the information is not multiplied by the many row you have.

 

When you are refering to filter by a column what do you mean, I cannot see any filter column on the image.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Frequent Visitor

Re: 100 % Stacked bar chart

Hi @MFelix ,

Sorry for not providing the entire data set.
For example I have a Portfolio column here, i want to use this as a filter.

Also, each record represents an application. Unpivoting increases the number of these records and I get a wrong count.

2020-01-21_14h30_56.png

 Is there any other way of getting this done?

 

Kind Regards,
Adarsh

Highlighted
Super User III
Super User III

Re: 100 % Stacked bar chart

Hi @ahumne25 ,

 

Do you have any specific column that you use as ID? If yes I would split your model in two table one with all the Portfolio information that you will not unpivot and delete all the columns A to I and a second table that would be the ID column and the unpivot of the A to I columns then linked both tables by the ID column.

 

To calculate the status you would use the 2nd table and for the rest of the setup the other table.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Frequent Visitor

Re: 100 % Stacked bar chart

Thanks a lot for this. Now i can get the exact visual I wanted. 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors