Creating custom rows for addition and subtration

[lux4545]

I was tasked with replicating an excel file so for instance I have line items below. *Note this is oversimplified for an example. 

Banking Cost: $10

Asset Lending: Banking cost - revenue = $4

__________________

Revenue    $ 6

My dataset in PowerBI provides me the line items banking cost and revenue but not asset lending. (I am missing multiple line items that they caculated from excel through subtraction or addition of values). I am unsure of how to add the field to my query and then create a matrix that also has that value in there. Also not sure if the logic is similar for adding line items or if there's a formula/measure I can add to my matrix table to have custom lines calculated from other rows. 

My goal is to figure out if there's a formula I can add to my query or if I can create a separate table that maps out the values, etc

Thank you!

[CoreyP]

You should transform your source data so that it's in the best format for Power BI. Those rows should be pivoted into columns. Then you can easily create calculated columns / measures of those columns.

[amitchandak]

@lux4545 , I have achieved this using additional row in dimesion

please check

Power BI How to get the P&L formatting right: https://youtu.be/C9K8uVfthUU
Power BI How to get two columns format Profit and Loss Statement(P&L) right: https://youtu.be/WLg85yiMgHI
https://medium.com/microsoft-power-bi/power-bi-formatted-p-l-with-custom-sub-totals-and-blank-rows-e...

[lux4545]

To add additional context, my data is coming from a database not excel. There are thousands of rows and I cannot simply make each line item a separate column and then create a calculated field from there. I watched the youtube videos but it looks like fields are being calculated in excel verus query.  The formatting portion is helpful for the youtube videos though-I did not know how to do that. 

Any other ideas or if I need to clarify in more detail, please let me know

[amitchandak]

@lux4545 , Usually when something is not available in dimension we try to add that value.

Assume I have an account table

Account Id, Account Name

1, A

2, B

I need an account C that is missing

Then I will create table using enter data or Source SQL or excel having data

Account Id, Account Name

3, C

Then I will append the two to have dimension

One does not need to have extended dimensions as I have, but you need a Dimension/master table

In the actual project I have appended the tables to have additional rows(The second table was manually data entry for around 15 columns)