Forum Discussion
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 can make it happen on excel using PivotTables, but it seems to be impossible on Power BI. Is there a workaround? Could someone be able to explain to me how it's done?
Appreciate!
Tadas
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.
4 Replies
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.
Thank you for your prompt reply!
In a graph, can you sort the months as they show in the table? Jan, Feb, Mar...
Thanks!
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.
