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.

     

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

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'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?

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

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

  • 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.