PowerBI Matrix

[leonghio]

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

When I create a matrix in PowerBI, I will see this table. 

If I drill up the matrix, it will add up the Budget and the Actual, which does not make sense.

How do I prevent it from adding up?

Or if it is possible to disable the matrix from drill up / collapsing?

Or is there another way of presenting the data?

Thanks.

[Greg_Deckler · Accepted solution]

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.