Forum Discussion

ManOAction's avatar
ManOAction
New Member
8 years ago
Solved

Calculated Row in Matrix

I'm brand new to Power BI and I'm having a hard time figuring this out.   I'm building a financial report, and I'd like to include a calculated row (not a column).  The data is expense_type (rows) ...
  • ManOAction's avatar
    ManOAction
    8 years ago

    That makes sense.   

     

    I've written it into the underlying query in data warehouse as a union select, and it's working fine.

     

    Thanks so much for your help.

TomMartens's avatar
TomMartens
Super User
8 years ago

Okay, understand!

 

Unfortunately this won't work, due to the fact that you can't create a Calculated Member in DAX, as it is possible in MDX, and assign this member to an already existing column. Using the tabular model this would necessarily lead to adding more rows to the table.

 

Regards

Tom

 

ManOAction's avatar
ManOAction
New Member
8 years ago

That makes sense.   

 

I've written it into the underlying query in data warehouse as a union select, and it's working fine.

 

Thanks so much for your help.

  • mede's avatar
    mede
    Resolver I
    8 years ago

    Just for others who can't do it prior importing into Power BI, here is one suggested solution.

     

    This solution can be brutal, and may be even risky depending on your data model, but in simple cases it will work.

     

    Lets assume you have a table called "Income" with columns Transaction Type and Amount.

     

    Firstly, you need to duplicate your query, name it "Brokerage". Secondly, in the "Brokerage" table, filter your Transaction Type column with only "Ad Revenue". Thirdly, from Transform Tab, multiply your "Amount" with the brokerage rate. Fourthly, replace values in Transaction Type column from "Ad Revenue" to "Brokerage Commission"

    Finally, you need to browse back to your original "Income" table and "Append Query" (append Brokerage to Income)

     

    Here is a reproducable example:

    Income Table

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxRCEotS80rTVXSUTI0AAOlWB2wRHBiTmoxUNgULuibWZwMFDCGCMQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Transaction Type"}, {"Column2", "Amount"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Amount", type number}}),
    #"Appended Query" = Table.Combine({#"Changed Type1", Brokerage})
in
    #"Appended Query"

    Brokerage Table;

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxRCEotS80rTVXSUTI0AAOlWB2wRHBiTmoxUNgULuibWZwMFDCGCMQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Transaction Type"}, {"Column2", "Amount"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Amount", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Transaction Type] = "Ad Revenue")),
    #"Multiplied Column" = Table.TransformColumns(#"Filtered Rows", {{"Amount", each _ * 0.2, type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Multiplied Column","Ad Revenue","BrokerageCommission",Replacer.ReplaceText,{"Transaction Type"})
in
    #"Replaced Value"