Financial Reporting in PowerBi


#1

In my PnL statements, it breakdowns into few main categories as follow:-
-Revenue
-COGS
-Other Income
-Staff Cost
-Other Expenses

Please advise how could I insert Gross Profit (ie Revenue - COGS) in the PnL statements in PowerBi. In addition, how could I present the above in sequence and add in the additional row measures of gross profit and net profit.

Thanks.


#2

Unfortunately there is no easy way to complete this currently.

I’ve seen a way to create subtotals but I don’t particular like it that much as it’s way too complex.

The best examples I have currently are below. I will also be covering financial reporting again during the next learning summit event.

This is the best blog post I’ve seen on the subject, but for me it’s too complex, so I try to simplify it quite a bit.

I’m sure MS will be looking improving this (hopefully asap)

I’m certainly continuing to look for the best solution here. As soon as I have one I will be sharing it with all members.

Chrs
Sam


#3

Hi Sam,
I tried recreating P/L by applying techniques shown in “Financial Reporting in Power BI - March 2018” tutorial video. It’s a general success, however, there are quite a few observations where I would like to seek your advice/opinion:

  1. for expenses, % variance sign (this year - last year / last year) is worked out correctly for all those expenses if this year number is more than the last year. however, when this year number is less than last year it shows % variance with wrong sign (like in my case: this year number is - 2198, last year number is - 2748; whereby - denotes expenses, % variance should have appeared as +20% but it appears -20%
  2. if last year expense number, due to certain adjustments/reversals, appears as +ve instead of -ve and this year number is -ve (like in my case: this year number is - 820, last year number is +286; whereby - denotes expenses, % variance should have appeared as -ve% but it appears 387%

Would appreciate your support in this matter.

Regards,
AM


#4

I’ve looked at the model to review this and it seems to be calculating as it should for me.

Can you show a specific example with images so I can see the results.

I can’t replicate the issues here by using the example model.

Thanks


#5

Thank you Sam, for a usual quick response.
Pls see the image pasted below:

image

formulas used are:
% Var =
SWITCH( TRUE(),
[Selected Metric] = “vs Last Year”, [% Var. to LY],
[Selected Metric] = “vs Budget”, [% Var. to Budget],
[% Var. to Budget] )

% Var. to LY =
IF( ISBLANK( [LY Actual ('Mln)] ),
BLANK(),
DIVIDE(ABS([Actual ('Mln)] - [LY Actual ('Mln)]) , [LY Actual ('Mln)] , 0 ) )

Pls suggest if any additional info is required.

Regards,
AM


#6

Why do you have the ABS there?

I’m not sure that is needed here and is likely why it’s causing the problem I think.


#7

Hi Sam,

If I take ABS out of this measure then the situation even gets worse like negative cost variance appears as +ve, pls see image below:

image

As per my data set, revenues and other income are with -ve signs whereas expenses are with +ve sign, hence i need to put ABS in my measure.

My “Actual” measure is calculated as:
Actual ('Mln) =
VAR Revenue = CALCULATE( SUM( FinData[Financial Results] ), ‘FinData’[Status] = “Actual”,
FILTER( ‘Financial Categories’, ‘Financial Categories’[Category] = “Revenue” ) ) * -1

VAR OtherIncome = CALCULATE( SUM( FinData[Financial Results] ), ‘FinData’[Status] = “Actual”,
FILTER( ‘Financial Categories’, ‘Financial Categories’[Category] = “Other Income” ) ) * -1

VAR Expenses = CALCULATE( SUM( FinData[Financial Results] ), ‘FinData’[Status] = “Actual”,
FILTER( ‘Financial Categories’, ‘Financial Categories’[Category] = “Expenses” ) )
RETURN
DIVIDE( Revenue + OtherIncome - Expenses , 1000000, 0 )

I so look forward to have your guidance for the resolution of this issue.

Regards,
AM


#8

Ok got it, it does look like quite a problem.

I think if we make some adjustment to the denominator it should work.

Try placing the ABS around the denominator here like so.

DIVIDE([Actual ('Mln)] - [LY Actual ('Mln)] ,ABS( [LY Actual ('Mln)] ), 0 ) )

I think this should do it. Let me know if not


#9

Thank so much Sam. You truly are a “BIGenius”. Issue is settled now.

Just wanted to know about your plans to cover Balance Sheet and Cash Flow Statements, historical as well as forecast, it would be a great help to many of us.

Regards,
AM


#10

Ok great.

Yes definitely have plans in the near future for content on those topics. Watch out for announcements in the coming months