topic Re: How to Calculate Returns in DAX Commands and Tips

How to Calculate Returns

Zaynah16 — Thu, 18 Nov 2021 11:59:00 GMT

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

Re: How to Calculate Returns

VahidDM — Thu, 18 Nov 2021 12:12:36 GMT

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/

Re: How to Calculate Returns

Zaynah16 — Thu, 18 Nov 2021 12:57:59 GMT

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

Re: How to Calculate Returns

Anonymous — Mon, 22 Nov 2021 10:14:07 GMT

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.

Re: How to Calculate Returns

Zaynah16 — Mon, 22 Nov 2021 13:02:42 GMT

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

Re: How to Calculate Returns

Anonymous — Tue, 23 Nov 2021 05:51:00 GMT

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 Scalar Value.

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.

Re: How to Calculate Returns

Zaynah16 — Fri, 26 Nov 2021 08:04:43 GMT

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

Re: How to Calculate Returns

Zaynah16 — Fri, 26 Nov 2021 13:34:23 GMT

Hi Rico,

Thank you for your assistance this has helped.

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

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