Financial Reporting in PowerBi - custom subtotals

Power BI
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.

1 Like
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.

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,

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, 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,

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

11

Recently I’ve created some new and relevant content on this particular topic of indenting sub-totals.

Check out the below video tutorials

Also during this live member only workshop I really detailed a financial reporting templates technique which solves this for Power BI users looking to create specific financial reports.

See below

Chrs
Sam

12

Hi All
I am Fabio and working on big data advanced analytics. I am new with PowerBI and despite I really enjoy learning and working with it I am still struggling for some really “easy” stuff. I would like to ask your support about the following cumulative calculation:

Check.xlsx (21.0 KB) 200727_ACE_Quality_Targets.pbix (356.6 KB)

I need to calculate the values of scrap over a process. In attachment you can find the PowerBI Model

  1. I Have 100 product and for each product I have “Y” Process step to produce it (let´s take as example the “product 10” – with 5 process steps)

  2. The values (cost) of my product increase for each process step. For this reason I have to consider the cumulative cost for the scrap cost calculation

  3. The Value of my product is give by the sum of “BOM cost” + “Labour” + “Process cost”

  4. Set Up Scrap cost per one pc produced are = Set up scrap * Cumulative total cost without scrap / Batch size

  5. When I calculate the “Cumulative total cost without scrap” the calculation is correct but when I used this measure to calculate the “Set up Scrap cost” I got wrong number

PowerBI use the column of “Total cost without scrap” instead of the “ Cumulative total cost without scrap” despite I have specified the right measure

See “check.xlsx” file column “T” (the correct calculation) and column F (the wrong PowerBI Calculation) / Column U (Check wrong calculation).

I can not understand where I am mistaking.

Your help will be really appreciated

Thanks in advanced

13

Hi @Fabio_Ghirardi,

You might get a faster response if you create a new posting/topic as this posting has a solution to it.

The solution to this posting was back in 2018/2019.

thanks
Keith