How to calculate a return

Question

Hi everyone,&nbsp;Please could i get help to calculate a Return for each of the 5 years from revenue, cost savings, and cost incurred.&nbsp;&nbsp;I've included sample data as well as the output required.&nbsp;&nbsp;Calculation&nbsp;Revenue 2021&nbsp; + Cost Savings 2021 - Costs incurred 2021= Return 2021&nbsp;&nbsp;Data&nbsp;Year 2021&nbsp; Year 2022&nbsp; &nbsp; Year 2023&nbsp; &nbsp; &nbsp; Year 2024&nbsp; &nbsp; &nbsp; Year 2025&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; KPI&nbsp;10 000&nbsp; &nbsp; &nbsp; &nbsp;20 0000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15 000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 56 000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12 000&nbsp; ( Revenue )&nbsp; &nbsp;Decrease potable water comsuption&nbsp;8 000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;90000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10 000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12 000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;17 000 ( Cost Savings)&nbsp;Decrease potable water comsuption5 000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6 000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7 000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8 0000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9 000 ( Costs Incurred)&nbsp;Decrease potable water comsuption10 000&nbsp; &nbsp; &nbsp; &nbsp;20 0000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15 000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 56 000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12 000&nbsp; ( Revenue )&nbsp; &nbsp;Decrease discharge sea to sewer&nbsp;8 000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;90000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10 000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12 000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;17 000 ( Cost Savings)&nbsp;Decrease discharge sea to sewer5 000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6 000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7 000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8 0000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9 000 ( Costs Incurred)&nbsp;Decrease discharge sea to sewer&nbsp;&nbsp;&nbsp;&nbsp;Required output&nbsp;&nbsp;

v-lianl-msft · Accepted Answer

You need to change your data model.
1. Select the column then unpivot other column.

2. Then create a measure like below:

&nbsp;
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
&nbsp;
&nbsp;

Forum Discussion

How to calculate a return

1 Reply

Related content

How to make a DAX calculation return BLANK rows

Dynamic Market Return Calculations

If Statement Calculated Column Returning Incorrectly

Calculations with measures return odd results

CALCULATE returns blank values