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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nick9000
Frequent Visitor

Build a PnL (P&L Profit and Loss Income Statement) NAV input

Hi All,

 

I'm reasonably new to PowerBI and looking to set up some PnLs from a Nav table. The result I'm after is similar to below. 

My current tables are:

NAV GL

Date

Chart of Accounts

 

These produce the correct rows and financial data, however it is not looking as good as it could. 

 

Capture.PNG

Current Issues:

Account Order - I currently have this as a separate number column on the visualisation table, so the correct PnL order can be presented. (These numbers are assigned to accounts in the Chart of Accounts table).

Debits and Credits - Credits are negative and debits are positive number, when these are summed the provide the correct result.

 

Ideal result:

Account Order - Sort by account type appearing in the assigned order from the Chart of Accounts table. Does this get in assigned in the background somehow? I imagine the mapping will remain on the Chart of Accounts table but will allow the visualisation to be sorted by the account type rather than a separate column. 

 

Debits and Credits - Display all as positive, while calculating profit correctly. I thought I may be able to change the formatting so negatives have the minus sign removed, although when I tried this the visualisation would sum all as positive.

 

 

Thanks in advance.

 

Cheers

 

Nick

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi Nick,

 

Sorting:  Go to the Data area of your Power BI Desktop and select the columns you wish to use in your table, which i'm expecting to be Account Type.  Go to the Modeling area of the ribbon and click on the button "Sort by Column" and set that to be the numeric column you mentioned.

Displaying Debit/Credits:  Create a display metric that shows debits and credits, then converts them to an Absolute figure.  This could be as simple as "Actual Display = ABS(SUM('YourTable'[Actual]))"

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi Nick,

 

Sorting:  Go to the Data area of your Power BI Desktop and select the columns you wish to use in your table, which i'm expecting to be Account Type.  Go to the Modeling area of the ribbon and click on the button "Sort by Column" and set that to be the numeric column you mentioned.

Displaying Debit/Credits:  Create a display metric that shows debits and credits, then converts them to an Absolute figure.  This could be as simple as "Actual Display = ABS(SUM('YourTable'[Actual]))"

Hello Sir,

 

I need help on to create simple profit and loss calculation in power bi by row wise from excel table as below, i.e.   Kindly someone can help me with details please?

 

Accounts                    Amount

 

Cost                            50000

Revenue                   100000

Gross Profit                50000

Profit                              500

Sorry i need it in column wise pls

Worked perfectly, thanks Ross!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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