Forum Discussion

PhiBu's avatar
PhiBu
Icon for Helper I rankHelper I
6 years ago
Solved

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 stack...
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    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.

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 years ago

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's avatar
PhiBu
Icon for Helper I rankHelper I
6 years ago

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: 

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

 

Best regards and thanks in advance!

 
  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    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.