Calculate Gross profit, EBITDA, Net Profit and YTD based on this two columns

[oolamide85]

Dear All,

I would like to calculate Gross profit, EBITDA, Net Profit and YTD based on this two columns,

Gross Profit = Turnover + Cost of Sales

EBITDA = Salaries + Other overheads - Gross Profit

Net profit = Non trading/exceptional + Depreciation + Tax - EBITDA

Below is my Sample data: Sample Data

 Expected Output

Expected Output Month on Month

I need all help I can get please.

Thanks.

[v-cherch-msft · Accepted solution]

Hi @oolamide85

You may refer to below measures. You may use TOTALYTD Function for YTD. Here is the document for you.

Gross profit =
CALCULATE (
    SUM ( 'Sample'[Value] ),
    FILTER ( 'Sample', 'Sample'[L1] IN { "Turnover", "Cost of Sales" } )
)

EBITDA =
CALCULATE (
    SUM ( 'Sample'[Value] ),
    FILTER ( 'Sample', 'Sample'[L1] IN { "Salaries", "Other overheads" } )
)
    - [Gross profit]

Net profit =
CALCULATE (
    SUM ( 'Sample'[Value] ),
    FILTER (
        'Sample',
        'Sample'[L1] IN { "Non trading/exceptional", "Depreciation", "Tax" }
    )
)
    - [EBITDA]

Regards,

Cherie

[v-cherch-msft · Accepted solution]

Hi @oolamide85

You may refer to below measures. You may use TOTALYTD Function for YTD. Here is the document for you.

Gross profit =
CALCULATE (
    SUM ( 'Sample'[Value] ),
    FILTER ( 'Sample', 'Sample'[L1] IN { "Turnover", "Cost of Sales" } )
)

EBITDA =
CALCULATE (
    SUM ( 'Sample'[Value] ),
    FILTER ( 'Sample', 'Sample'[L1] IN { "Salaries", "Other overheads" } )
)
    - [Gross profit]

Net profit =
CALCULATE (
    SUM ( 'Sample'[Value] ),
    FILTER (
        'Sample',
        'Sample'[L1] IN { "Non trading/exceptional", "Depreciation", "Tax" }
    )
)
    - [EBITDA]

Regards,

Cherie

[balaganeshv2201]

good easy to understand

[oolamide85]

Thanks for your help, Gross Profit works perfectly well but, am still having problem with the EBITDA below is the error message:

The value for 'GrossProfit' cannot be determined. Either 'GrossProfit' doesn't exist, or there is no current row for a column named 'GrossProfit'.

 EBITDA=
CALCULATE
(
SUM
('Finance 2'[VALUE]),
FILTER('Finance 2','
Finance 2'[Level1] IN
{"Salaries","Other Overheads"}
)
)
-[GrossProfit]

[v-cherch-msft · Accepted solution]

Hi @oolamide85

You may create measures for them. Here is the sample file for your reference.

Regards,

Cherie

[oolamide85]

How can I have the report visual dipslayed in the format below?