cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

P&L Finance report

Hi,

 

I have a Finance report for which i need to calculate P&L, can you please help me in the calculation, below is the screenshot of my report. 

 

neelofarshama_0-1600946848018.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: P&L Finance report

@neelofarshama 

If you need the difference between Revenue and Expense, try:

 

Difference = 
VAR Rev = CALCULATE([Sum of Values], FILTER(Table, Table [Acct Type] = "Revenue")) 
VAR Exp = CALCULATE([Sum of Values], FILTER(Table, Table [Acct Type] = "Expense")) 
RETURN
Rev-Exp

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

7 REPLIES 7
Highlighted
Super User II
Super User II

Re: P&L Finance report

@neelofarshama 

If you need the difference between Revenue and Expense, try:

 

Difference = 
VAR Rev = CALCULATE([Sum of Values], FILTER(Table, Table [Acct Type] = "Revenue")) 
VAR Exp = CALCULATE([Sum of Values], FILTER(Table, Table [Acct Type] = "Expense")) 
RETURN
Rev-Exp

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Highlighted
Helper IV
Helper IV

Re: P&L Finance report

Hi,

Thank you for the reply but can you be precise and explain me what values go for 

CALCULATE([Sum of Values]

also I need running totals for my data 

Highlighted
Super User II
Super User II

Re: P&L Finance report

@neelofarshama 

Apologies for being vague...

[Sum of Values] refers to the measure you are using in your matrix in the values bucket (so whatever measure you are using)

As for the running totals, can you please provide more info? (a depiction of what you expect to see, the strucure of your model, the fields you are using in the matrix visual as rows/columns...). Running totals depend on the filter context, so the pertinent info is essential.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Highlighted
Helper IV
Helper IV

Re: P&L Finance report

Thank you so much your DAX for Rev-Exp worked. 

I will be grateful if you help me in achieving the P&L report like the one below, where the Revenue is summed up like thefirst row in the table, rather than showing subtotals like the my previous image

neelofarshama_0-1601022503404.png

 

Highlighted
Helper IV
Helper IV

Re: P&L Finance report

Thank you so much this worked

Highlighted
Helper IV
Helper IV

Re: P&L Finance report

Thank you Paul for the help, the formula worked, I have another question

1) Can we make Expense all +ve(postive sign), but still have Revenue-Expense -ve(negative sign) in the below report

2) Can we control the order of different Revenue and Expense types in the same report

 

neelofarshama_0-1601207958039.png

Looking forward for the reply

 

Thanks in Advance

Highlighted
Super User II
Super User II

Re: P&L Finance report

@neelofarshama 

I'm not too sure what you mean. If your expenses are -ve values and you want to display them as such all you have to do is change the measure to:

 

Difference = 
VAR Rev = CALCULATE([Sum of Values], FILTER(Table, Table [Acct Type] = "Revenue")) 
VAR Exp = CALCULATE([Sum of Values], FILTER(Table, Table [Acct Type] = "Expense")) 
RETURN
Rev + Exp

 

Notice how the "return" now has a "+

If, however, your Expenses are -ve values, but you want to display them as +ve values you have 2 options:

1) Create a new measure and multiply the [Sum of Expenses] *-1. In this case the value itself changes to +ve, so the [Difference] measure must remain Rev - Exp

2) Simply change the display format for the original measure: you can change the display format for the measure under the modeling view. Select the measure in the model view, go to properties, Formatting and select custom. The second expression in the string corresponds to the -ve values. Change it so that it is the same as the +ve values string.

reformatting.JPG

 This will ONLY change the display format; the value actually remains -ve (see depiction below: the measure [Check formatted values] is multiplying the Formatted measure *1), so the [Difference] measure must be Rev + Exp

Here is depiction of the options:

-ve formatting result.JPG

 

As regards the order of the rows, the best way to do this is to include an "Order" column in the Dimension tables for "Revenue" and "Expenses":

1) This is easy to do in Power Query: create a conditional column in each dimension table and establish the order value for each of the rows.
2) Alternatively you can do this in Excel by recreating the dimension tables in Excel, write the order values for each row, import the tables to the model to become your dimension tables and establish a one-to-many relationship with the fact tables).

Then go to the tabel view for the dimensions, select the "Revenue" and "Expense" columns, and use the function in the ribbon "Sort column by" and choose the "Order" column. 

Let us know if you get stuck!





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors