# Financial Reporting in PowerBi - custom subtotals

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

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

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

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

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

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

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.

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:

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

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

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

Ok great.

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

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

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