Forum Discussion

leonghio's avatar
leonghio
Frequent Visitor
8 years ago
Solved

PowerBI Matrix

PowerBI Question. How to prevent the Budget / Actual dimension from adding up in PowerBI Matrix? Eg, I have a Budget / Actual dimension, a Time dimension and an Operating Costs dimension   ...
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    I would unpivot your last three columns like so:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bYwxC8IwEEb/itxcDpPGXB11d+pYMoR4lYBkkMvQf2+9QrXF5XsH93jDALeYi3CJJTE0cK33B8t8WPROQccFDkKzty9Janzq37SKE81o0Vi1e071lWX6DSPZze68NYn0KaI/63aLl4UPvcRx3BRtt65B90f9Ri3pelWNhxDe", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Operating Cost" = _t, #"Budget/Actual" = _t, #"Apr-17" = _t, #"May-17" = _t, #"Jun-17" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Operating Cost", type text}, {"Budget/Actual", type text}, {"Apr-17", type number}, {"May-17", type number}, {"Jun-17", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Operating Cost", "Budget/Actual"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Month"}})
in
    #"Renamed Columns"

    Then you could create a nice clustered column chart with Operating Cost and Month in your Axis, Budget/Actual in your Legend and Value in your Value.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

I would unpivot your last three columns like so:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bYwxC8IwEEb/itxcDpPGXB11d+pYMoR4lYBkkMvQf2+9QrXF5XsH93jDALeYi3CJJTE0cK33B8t8WPROQccFDkKzty9Janzq37SKE81o0Vi1e071lWX6DSPZze68NYn0KaI/63aLl4UPvcRx3BRtt65B90f9Ri3pelWNhxDe", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Operating Cost" = _t, #"Budget/Actual" = _t, #"Apr-17" = _t, #"May-17" = _t, #"Jun-17" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Operating Cost", type text}, {"Budget/Actual", type text}, {"Apr-17", type number}, {"May-17", type number}, {"Jun-17", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Operating Cost", "Budget/Actual"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Month"}})
in
    #"Renamed Columns"

Then you could create a nice clustered column chart with Operating Cost and Month in your Axis, Budget/Actual in your Legend and Value in your Value.