How to calculate a return

[Zaynah16]

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 

[V-lianl-msft · Accepted solution]

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