How to Calculate Returns

[Zaynah16]

Hi Guys, 

I want to calculate a return value calculated from Revenue plus cost savings minus cost incurred, from a single tab on excel for the 5 years. 

Thanks 

Zaynah

[Zaynah16]

Hi Rico, 

Thank you for your assistance this has helped. 

I am now trying to apply a filter across multiple tables. 

I have calculated Return on Investment as below however revenue, cost Savings and cost incurred are from the annual Returns table and spend to date is from another table and therefore reflecting incorrecly. Please help

Revenue+Cost_savings-Cost_incurred/SpendToDate*100

DAX

ROI =

VAR _Revenue = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Revenue"))

VAR _Cost_savings = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Cost savings"))

VAR _Cost_incurred = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Cost incurred"))

VAR _SpendToDate = CALCULATE(SUM('SDG Goals'[Spend To Date]))

VAR _ROI = ((_Revenue+_Cost_savings-_Cost_incurred)/_SpendToDate)*100

RETURN

_ROI

[VahidDM]

Hi @Zaynah16 

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[Zaynah16]

Hi Vahid, 

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

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 

Thanks 

Zaynah 

[Anonymous · Accepted solution]

Hi @Zaynah16 ,

Here I suggest you to transform your table and try my measure to achieve your goal.

M Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAiUdJSMDKMPQFEKbmkH5RhDaJTW5KDWxOFWhIL8kMSknVaE8sSS1SCE5P7e4tKAkMz9PKVYnWskCotgSZpgBqiGG5qQYBnUJ1CFQrRYGCCuINol0X6ZkFidnJBalpyoUpyYqlOQDqXKguWR5Ep9ZJPkRp0GxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Year 2021" = _t, #"Year 2022" = _t, #"Year 2023" = _t, #"Year 2024" = _t, #"Year 2025" = _t, KPI = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year 2021", Int64.Type}, {"Year 2022", Int64.Type}, {"Year 2023", Int64.Type}, {"Year 2024", type text}, {"Year 2025", Int64.Type}, {"KPI", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"KPI"}, {{"Rows", each _, type table [Year 2021=nullable number, Year 2022=nullable number, Year 2023=nullable number, Year 2024=nullable text, Year 2025=nullable number, KPI=nullable text]}})
     ,Indexed = Table.TransformColumns(#"Grouped Rows", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
    #"Expanded Rows" = Table.ExpandTableColumn(Indexed, "Rows", {"Year 2021", "Year 2022", "Year 2023", "Year 2024", "Year 2025", "GroupIndex"}, {"Rows.Year 2021", "Rows.Year 2022", "Rows.Year 2023", "Rows.Year 2024", "Rows.Year 2025", "Rows.GroupIndex"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Rows", {"KPI", "Rows.GroupIndex"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","Rows.Year ","",Replacer.ReplaceText,{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Attribute", "Year"}, {"Rows.GroupIndex", "GroupIndex"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}, {"Value", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if [GroupIndex] = 1 then "Revenue" else if [GroupIndex] = 2 then "Cost Savings" else "Costs Incurred"),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Type"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"GroupIndex"})
in
    #"Removed Columns"

Measure:

Measure = 
VAR _Revenue = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Revenue"))
VAR _Cost_Savings = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Cost Savings"))
VAR _Costs_Incurred = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Costs Incurred"))
VAR _RETURN = _Revenue+_Cost_Savings-_Costs_Incurred
RETURN
_RETURN

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Zaynah16]

Hi Rico, 

Many Thanks for the help, I've tried the above however the code does not run and gives me the following error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Kind Regards 

Zaynah

[Anonymous]

Hi @Zaynah16 

This error occurs whenever the DAX engine was expecting a single value, or scalar, and instead received a table of values instead. This is an easy error to make because many DAX functions, such as FILTER, SUMMARIZE and ALL, return table values. 

For reference: DAX Error: The Expression Refers to Multiple Columns. Multiple Columns Cannot Be Converted to a Scal...

My code is a sample to show you how to transform your table. If you want to use M code, you can try to create a blank query in Power Query Editor and then copy and paste M code in Advanced Editor.

Then build a measure by my dax code. My dax code seems don't contain that error, please check your code referring to above blog. 

Or you can download my sample an compare the difference between mine and yours.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Zaynah16]

Hi Rico, 

Thank you for your assistance this has helped. 

I am now trying to apply a filter across multiple tables. 

I have calculated Return on Investment as below however revenue, cost Savings and cost incurred are from the annual Returns table and spend to date is from another table and therefore reflecting incorrecly. Please help

Revenue+Cost_savings-Cost_incurred/SpendToDate*100

DAX

ROI =

VAR _Revenue = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Revenue"))

VAR _Cost_savings = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Cost savings"))

VAR _Cost_incurred = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Cost incurred"))

VAR _SpendToDate = CALCULATE(SUM('SDG Goals'[Spend To Date]))

VAR _ROI = ((_Revenue+_Cost_savings-_Cost_incurred)/_SpendToDate)*100

RETURN

_ROI