Clustered Stacked Column Chart

[JoaoOliveira7]

I need to replicat this chart on PBI.

I have the total number of cases, divided by category, until April 2022 and the red rectangle shows a forecast of cases until the end of the year 2022.

Can anyone help?

[Anonymous]

Hi @JoaoOliveira7 

Can you provide some sample data or picture so that can provide more suggestion for you , the information you have offered is less.

Best Regards!

Yolo Zhu

[JoaoOliveira7]

Hello,

Here you have the table that feeds the chart.

[Anonymous · Accepted solution]

Hi @JoaoOliveira7 

The line can realize but if you want to realize display the forecast value in column chart, it cannot work. 

The solution to implement the line, you can refer to the following solution.

1.Put the following code to Advanced Editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg4JUdJRMjYEEmbGQMICxDI0MAKTFkqxOtFKQanJIB5IwsQIRhiZgQhLqIKUVCDPACRiCiIsQOpBCgwhJoT4hzj6wHQaGoMUGpqCLTIxAbNNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, #"2019" = _t, #"2020" = _t, #"2021" = _t, #"2022" = _t, #"Forest 2022" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"2019", Int64.Type}, {"2020", Int64.Type}, {"2021", Int64.Type}, {"2022", Int64.Type}, {"Forest 2022", Int64.Type}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"2019", "2020", "2021", "Forest 2022", "2022"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Only Selected Columns","Forest 2022","2022",Replacer.ReplaceText,{"Attribute"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Attribute", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Type"}, {{"Count", each _, type table [Type=nullable text, Attribute=nullable number, Value=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Attribute", "Value", "Index"}, {"Attribute", "Value", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if [Index] = 5 then "Forecast" else "Now"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Attribute", "Year"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Index"})
in
    #"Removed Columns1"

2.Create the following three measures

Sum = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Type]<>"Total"&&[Custom]="Now"))

Forest = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Type]="TOTAL"&&OR([Year]=2021,[Year]=2022&&[Custom]="Forecast")))

TOTAL = CALCULATE(SUM('Table'[Value]),'Table','Table'[Custom]="Now",'Table'[Type]="TOTAL")

Then put them to the visual field

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.

[JoaoOliveira7]

Hi,

Thanks for the feedback. Despite there is no solution to place the forecast in the columns, your solution for the line seems good to me.

Thanks,

Best Regatds,

João