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

[nick9000]

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. 

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

[Anonymous · Accepted solution]

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]))"

[dineshksh]

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

[dineshksh]

Sorry i need it in column wise pls

[nick9000]

Worked perfectly, thanks Ross!