Calculated Row in Matrix

[ManOAction]

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) by month (column).  Looks like this...

When I try to use DAX to create a percentage of one of the rows (% commission of ad sales), it puts it as an additional column.

The query I'm building this off of is laid out as `amount`, `type`, `date`.

How do I get the calculated information to appear as a row.

My current formula looks like this.

BrokerageCommission = CALCULATE(SUM('reports financialreport'[Amount])*.2,'reports financialreport'[Transaction Type] = "Ad Revenue")

[TomMartens]

Hey,

I guess your formula creates a measure, by default measures are put to the columns in a matrix visual:

If you want the measures used on rows, just mark the matrix visual and turn on the property "Show on rows"

Hope this is what you are looking for

Regards

Tom

[ManOAction]

Thanks, that's helpful feedback, but not quite what I need.

It's now repeatedly showing it as a subset of all rows.  I need it to show only once.  The same that a subtotal would work.

[Ashish_Mathur]

Hi,

This can be done in Excel by converting the PowerPivot table into cube formulas.

[TomMartens]

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 · Accepted solution]

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]

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"

[mede]

Well, if i understand you correctly, you'd like to calculate the total Brokerage for all transaction types combined, and show it as another row of Transaction Type.

For example like this:

Transaction Type, Jan, Feb, Mar, .... 

Ad Revenue

Ad Sales

.

.

.

BrokerageCommission

Total

Could you confirm that this is what you need? We can help better if we know precisely what is the end visual you'd like to get.