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

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.

     

8 Replies

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

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

     

    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.