cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Zaynah16
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 

Zaynah16_0-1637244024454.png

Zaynah16_1-1637244054055.png

 

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

You need to change your data model.

1. Select the column then unpivot other column.

Vlianlmsft_0-1637570216495.png

2. Then create a measure like below:

Vlianlmsft_1-1637570331009.png

 

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

 

 

View solution in original post

1 REPLY 1
V-lianl-msft
Community Support
Community Support

You need to change your data model.

1. Select the column then unpivot other column.

Vlianlmsft_0-1637570216495.png

2. Then create a measure like below:

Vlianlmsft_1-1637570331009.png

 

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

 

 

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Kudoed Authors