Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
lux4545
Frequent Visitor

Creating custom rows for addition and subtration

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!

 

 

 

4 REPLIES 4
CoreyP
Solution Sage
Solution Sage

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
Super User
Super User

@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...

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

 

 

@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)

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.