cancel
Showing results for
Did you mean:
Frequent Visitor

## How to calculate a return

Hi everyone,

Please could i get help to calculate a Return for each of the 5 years from revenue, cost savings, and cost incurred.

I've included sample data as well as the output required.

Calculation

Revenue 2021  + Cost Savings 2021 - Costs incurred 2021= Return 2021

Data

Year 2021  Year 2022    Year 2023      Year 2024      Year 2025                    KPI

10 000       20 0000         15 000          56 000           12 000  ( Revenue )   Decrease potable water comsuption

8 000         90000            10 000          12 000           17 000 ( Cost Savings) Decrease potable water comsuption

5 000         6 000             7 000            8 0000            9 000 ( Costs Incurred) Decrease potable water comsuption

10 000       20 0000         15 000          56 000           12 000  ( Revenue )   Decrease discharge sea to sewer

8 000         90000            10 000          12 000           17 000 ( Cost Savings) Decrease discharge sea to sewer

5 000         6 000             7 000            8 0000            9 000 ( Costs Incurred) Decrease discharge sea to sewer

Required output

1 ACCEPTED SOLUTION
Community Support

You need to change your data model.

1. Select the column then unpivot other column.

2. Then create a measure like below:

``````Return =
var revenue = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),'Table'[Status]=" Revenue"))
var Cost_Savings = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),'Table'[Status]=" Cost Savings"))
var  Costs_Incurred = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),'Table'[Status]=" Costs Incurred"))
return revenue+Cost_Savings-Costs_Incurred``````

Community Support

You need to change your data model.

1. Select the column then unpivot other column.

2. Then create a measure like below:

``````Return =
var revenue = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),'Table'[Status]=" Revenue"))
var Cost_Savings = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),'Table'[Status]=" Cost Savings"))
var  Costs_Incurred = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),'Table'[Status]=" Costs Incurred"))
return revenue+Cost_Savings-Costs_Incurred``````

Announcements