Search code examples
powerbicolumn-chart

Stack column chart in PowerBI with date on x-asix and count on y-axis

I have a data in below format and looking to get a stacked column chart as below. I am not sure how to club the dates and count together which are in multiple columns.

My data

enter image description here

I want to represent the above data in a clustered column chart where data on X-axis is clubbed in month and on Y-axis is clubbed with count of Yes per month like below based on different project types.

enter image description here

Solution

  • You will need to transform your data, add a Date table, add a relationship, and possibly a measure to do this.

    1. Transform your data
    You should unpivot your columns in PowerQuery to give you a table structure similar to this:

    Project Test Date Result
    A Test A 01/05/2023 Yes

    Here is an example query to do this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1Dcw1TcyMDIGciJTi4GkiT5QAMEFoVidaCUnKBuGDI31DcwQCkFKnEHC+hbIug2N9A0NUAQwdLkA2UbG+oaG6LYChVFEY2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Test A Date Approved" = _t, #"Test A Result" = _t, #"Test B Date Approved" = _t, #"Test B Result" = _t, #"Test C Date Approved" = _t, #"Test C Result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Test A Date Approved", type date}, {"Test A Result", type text}, {"Test B Date Approved", type date}, {"Test B Result", type text}, {"Test C Date Approved", type date}, {"Test C Result", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"),
    #"Date Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each Text.Contains([Attribute], "Approved")),
    #"Date Renamed Columns" = Table.RenameColumns(#"Date Filtered Rows",{{"Value", "Date"}, {"Attribute", "Test"}}),
    #"Date Replaced Value" = Table.ReplaceValue(#"Date Renamed Columns"," Date Approved","",Replacer.ReplaceText,{"Test"}),
    #"Result Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each Text.Contains([Attribute], "Result")),
    #"Result Renamed Columns" = Table.RenameColumns(#"Result Filtered Rows",{{"Attribute", "Test"}, {"Value", "Result"}}),
    #"Result Replaced Value" = Table.ReplaceValue(#"Result Renamed Columns"," Result","",Replacer.ReplaceText,{"Test"}),
    Merge = Table.NestedJoin(#"Date Replaced Value", {"Project", "Test"}, #"Result Replaced Value", {"Project", "Test"}, "ResultMerge", JoinKind.LeftOuter),
    #"Expanded ResultMerge" = Table.ExpandTableColumn(Merge, "ResultMerge", {"Result"}, {"Result"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded ResultMerge",{{"Date", type date}, {"Result", type text}})
in
    #"Changed Type1"

    2. Create a Date table
    There are multiple ways to do this. For example, you can create a Calculated Table with the following DAX:

    Dim Date = ADDCOLUMNS(
    CALENDARAUTO(),
    "Month", FORMAT([Date], "mmmm"),
    "Month#", MONTH([Date])
  )

    Then right-click on this new Dim Date table and Mark as date table, select Date as the Date column. Next, select Month column and in the ribbon select Sort by column and select the Month# column.

    3. Create a relationship
    Now create a relationship between your Dim Date table to your test results table on the Date columns in each. It should look like: enter image description here

    4. Create your chart
    Now you will be able to create your chart as show below. Select Show items with no data to see consecutive months. enter image description here