Forum Discussion

TadasMac's avatar
TadasMac
Regular Visitor
3 years ago
Solved

Stacked Columns with line Chart without changing the data structure

Hi, I have got this data source that I wouldn't like to change, but I would like to include a stacked bar chart with a line into Power BI report if possible. The example of my data is given below. I ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi TadasMac 

    You can refer to the following suggestion.The sample data is the same as yours.

    If you don't want to change the data structure, first you can create a new table

    Then create two measure

    Measure = var _jan=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Jan]),"Mon","Jan")
var _feb=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Feb]),"Mon","Feb")
var _mar=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Mar]),"Mon","Mar")
var _apr=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Apr]),"Mon","Apr")
var _May=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[May]),"Mon","May")
var _June=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Jun]),"Mon","Jun")
var _July=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Jul]),"Mon","Jul")
var _aug=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Aug]),"Mon","Aug")
var _sep=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Sep]),"Mon","Sep")
var _oct=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Oct]),"Mon","Oct")
var _dec=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Dec]),"Mon","Dec")
var _nov=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Nov]),"Mon","Nov")
var _union=UNION(_jan,_feb,_mar,_apr,_May,_June,_July,_aug,_sep,_oct,_nov,_dec)
return SUMX(FILTER(_union,[Status] in VALUES('Table'[Status])&&[Mon] in VALUES('Table (2)'[Mon])&&[Status]<>"Capacity"),[Jan])
    Capacity = var _jan=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Jan]),"Mon","Jan")
var _feb=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Feb]),"Mon","Feb")
var _mar=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Mar]),"Mon","Mar")
var _apr=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Apr]),"Mon","Apr")
var _May=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[May]),"Mon","May")
var _June=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Jun]),"Mon","Jun")
var _July=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Jul]),"Mon","Jul")
var _aug=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Aug]),"Mon","Aug")
var _sep=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Sep]),"Mon","Sep")
var _oct=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Oct]),"Mon","Oct")
var _dec=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Dec]),"Mon","Dec")
var _nov=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Nov]),"Mon","Nov")
var _union=UNION(_jan,_feb,_mar,_apr,_May,_June,_July,_aug,_sep,_oct,_nov,_dec)
return SUMX(FILTER(_union,[Status] in VALUES('Table'[Status])&&[Mon] in VALUES('Table (2)'[Mon])&&[Status]="Capacity"),[Jan])

    Then put the 'Mon‘  column of new table  to the x-axis' and put the measures to the value column

     

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi TadasMac 

You can refer to the following suggestion.The sample data is the same as yours.

If you don't want to change the data structure, first you can create a new table

Then create two measure

Measure = var _jan=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Jan]),"Mon","Jan")
var _feb=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Feb]),"Mon","Feb")
var _mar=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Mar]),"Mon","Mar")
var _apr=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Apr]),"Mon","Apr")
var _May=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[May]),"Mon","May")
var _June=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Jun]),"Mon","Jun")
var _July=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Jul]),"Mon","Jul")
var _aug=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Aug]),"Mon","Aug")
var _sep=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Sep]),"Mon","Sep")
var _oct=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Oct]),"Mon","Oct")
var _dec=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Dec]),"Mon","Dec")
var _nov=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Nov]),"Mon","Nov")
var _union=UNION(_jan,_feb,_mar,_apr,_May,_June,_July,_aug,_sep,_oct,_nov,_dec)
return SUMX(FILTER(_union,[Status] in VALUES('Table'[Status])&&[Mon] in VALUES('Table (2)'[Mon])&&[Status]<>"Capacity"),[Jan])
Capacity = var _jan=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Jan]),"Mon","Jan")
var _feb=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Feb]),"Mon","Feb")
var _mar=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Mar]),"Mon","Mar")
var _apr=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Apr]),"Mon","Apr")
var _May=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[May]),"Mon","May")
var _June=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Jun]),"Mon","Jun")
var _July=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Jul]),"Mon","Jul")
var _aug=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Aug]),"Mon","Aug")
var _sep=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Sep]),"Mon","Sep")
var _oct=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Oct]),"Mon","Oct")
var _dec=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Dec]),"Mon","Dec")
var _nov=ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Status],[Nov]),"Mon","Nov")
var _union=UNION(_jan,_feb,_mar,_apr,_May,_June,_July,_aug,_sep,_oct,_nov,_dec)
return SUMX(FILTER(_union,[Status] in VALUES('Table'[Status])&&[Mon] in VALUES('Table (2)'[Mon])&&[Status]="Capacity"),[Jan])

Then put the 'Mon‘  column of new table  to the x-axis' and put the measures to the value column

 

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

TadasMac's avatar
TadasMac
Regular Visitor
3 years ago

Thank you for your prompt reply!

In a graph, can you sort the months as they show in the table? Jan, Feb, Mar...

Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi TadasMac 

    Change the Table(2) to the following

    Table (2) = ADDCOLUMNS(GENERATESERIES(1,12,1),"Mon",FORMAT(DATE(2023,[Value],1),"mmm"))

    Then click month column and select 'sort by value'

     

    Then remove the month column from the x-axis, then reput it

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • TadasMac's avatar
      TadasMac
      Regular Visitor
      3 years ago

      Hi again Yolo Zhu. I have tried this same method with larger dataset. Please see the Link to File . It seems like that same method does not summarise the data well. Would you be able to help me understand the problem here?

       

      Thanks
      Tadas