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

Stacked column chart in clustern column chart

Hello everyone, 

 

I would like to have a stacked column bar within my stacked column chart. Is this possible?
My goal is to compare the estimated hours and working hours in our project in a stacked bar chart. However, I want to have a stacked column for the two categories of working hours.   

I hope you can help me with that. 

Best regards!

 

Actual chartActual chart

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: Stacked column chart in clustern column chart

Hi @PhiBu ,

 

Looking at you data what I did was the following:

 

  • Created a new table with the following values:
Category ID
Estimated 1
Internal/External 2

 

  • Added the folowing 3 measures:

 

Estimated = IF(SELECTEDVALUE('Selection'[ID]) = 1 ; SUM('Jira-Issues'[Estimate Time  [h]]]); BLANK())

External = IF(SELECTEDVALUE('Selection'[id]) = 2 ; SUM('Jira-Worklogs'[External Time Spent [h]]]); BLANK())

Internal = IF(SELECTEDVALUE('Selection'[ID]) = 2 ; SUM('Jira-Worklogs'[Internal Time Spent [h]]]); BLANK())

 

 

Now create a Stacked Column chart with the following setup:

  • Axis:
    • Month
    • Category
  • Values
    • Internal
    • External
    • Estimated

Final result is in attach PBIX file (file in May version of PBI).

 

Be aware that the Hierarchy on the X-Axis need to be kept due to the setup what you can due is to concatenet the labels.

 


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 IX
Super User IX

Re: Stacked column chart in clustern column chart

@PhiBu , you have check for custom visual for that, I doubt anything in power bi like that

 

https://community.powerbi.com/t5/Desktop/Clustered-stacked-column-chart/td-p/75515

http://www.defteam.com/stacked-clustered-bar-chart/

https://appsource.microsoft.com/en-us/marketplace/apps?product=power-bi-visuals



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User V
Super User V

Re: Stacked column chart in clustern column chart

Hi  @PhiBu ,

 

Not really sure if I understand from the image what are the values to sum but the option is to do the following:

  • Create a table with the two categories you want:

Type

Estimated
Actuals

 

Now create 2 measures:

Estimated = 
IF(SELECTEDVALUE(Table[Type]) = "Estimated"; SUM(Table[Estimated]); BLANNK() )

Internal = 
IF(SELECTEDVALUE(Table[Type]) = "Actuals"; SUM(Table[Internal]); BLANNK() )

External = 
IF(SELECTEDVALUE(Table[Type]) = "Actuals"; SUM(Table[External]); BLANNK() )

 

Now just add this 3 Measures on your stack chart and you should get internal on top of external and Estimated to the side.

 

Be aware that I'm assuming that you have a colum for estimated / interal / external but can be categories you just need to redo the measures accordingly.


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
Super User VII
Super User VII

Re: Stacked column chart in clustern column chart

@PhiBu you can achieve this by using In-hierarchy drill down and can get you the expected output, something like this

 

image.png

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!






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.





Highlighted
Frequent Visitor

Re: Stacked column chart in clustern column chart

Hello, 

 

Many thanks for the quick response. 
The problem is that the actuals (internal and external working times) and the estimated working hours are in different tables. For the x-axes in my diagram I use a label (string format) from the table that also contains the estimated working hours. There is a 1:n relationship between the "Estimated Hours Table" and the "Internal and External Working Hours Table".
The solution with the in-hirachie does not fit. Also the solution with @MFelix  did not work for me.
Any other ideas? I hope you can help me :).


With kind regards. 

 

 

Best regards. 

Highlighted
Super User V
Super User V

Re: Stacked column chart in clustern column chart

Hi @PhiBu ,

 

Using the process that I show should work but probably theres is the need to make some adjustments, can you share a mockup sample of ypur data and relationships?


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
Super User VII
Super User VII

Re: Stacked column chart in clustern column chart

@PhiBu that' the closest you can get from standard visuals, check for custom visual if there is a visual that can meet your requirement.






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.





Highlighted
Frequent Visitor

Re: Stacked column chart in clustern column chart

Hey @MFelix,

 

thanks for your help. It looks like that I dont have permission to upload attachments.

The data I used for my mockup sample are:

Jira-Issues

KeyMonthEstimated Time[h]
600020-May10
700020-May20
800020-May5
100020-April3
200020-April8
300020-April16

 

Jira-Worklogs 

KeyExternal Timespent[h]Internal Timespent[h]
60003 
60003 
6000 4
700010 
7000 10
7000 5
8000 5
100044
20008 
30004 
3000 8
30004 

 

I used the column key as relation and created a clustered column chart. The column Month is a sting, not a date value. The result looks like that: 
pbi.PNG
Hope you can copy these tables in MS-Excel and help me with my issue. 

 

Best regards and thanks in advance!

 
Highlighted
Super User V
Super User V

Re: Stacked column chart in clustern column chart

Hi @PhiBu ,

 

Looking at you data what I did was the following:

 

  • Created a new table with the following values:
Category ID
Estimated 1
Internal/External 2

 

  • Added the folowing 3 measures:

 

Estimated = IF(SELECTEDVALUE('Selection'[ID]) = 1 ; SUM('Jira-Issues'[Estimate Time  [h]]]); BLANK())

External = IF(SELECTEDVALUE('Selection'[id]) = 2 ; SUM('Jira-Worklogs'[External Time Spent [h]]]); BLANK())

Internal = IF(SELECTEDVALUE('Selection'[ID]) = 2 ; SUM('Jira-Worklogs'[Internal Time Spent [h]]]); BLANK())

 

 

Now create a Stacked Column chart with the following setup:

  • Axis:
    • Month
    • Category
  • Values
    • Internal
    • External
    • Estimated

Final result is in attach PBIX file (file in May version of PBI).

 

Be aware that the Hierarchy on the X-Axis need to be kept due to the setup what you can due is to concatenet the labels.

 


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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors