Helper I

4 years ago

Solved

How to Calculate Returns

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

Anonymous

4 years ago

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.

How to Calculate Returns.pbix21 KB

7 Replies

VahidDM

Super User

4 years ago

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

Helper I

4 years ago

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

Not applicable

4 years ago

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.

How to Calculate Returns.pbix21 KB

Zaynah16
Helper I
4 years ago
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

Forum Discussion

How to Calculate Returns

7 Replies

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