Stacked column chart in clustern column chart

[PhiBu]

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 chart

[MFelix]

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.

[amitchandak]

@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

[parry2k]

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

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!

[PhiBu]

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. 

[MFelix]

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?

[PhiBu]

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

Key	Month	Estimated Time[h]
6000	20-May	10
7000	20-May	20
8000	20-May	5
1000	20-April	3
2000	20-April	8
3000	20-April	16

Jira-Worklogs 

Key	External Timespent[h]	Internal Timespent[h]
6000	3
6000	3
6000		4
7000	10
7000		10
7000		5
8000		5
1000	4	4
2000	8
3000	4
3000		8
3000	4

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: 

Hope you can copy these tables in MS-Excel and help me with my issue. 

Best regards and thanks in advance!

[MFelix · Accepted solution]

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.

[parry2k]

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