Forum Discussion
Helper IHow to Calculate Returns
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 _RETURNResult is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
_RETURNResult 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.
Helper IHi 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
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 ZhouIf 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)*100RETURN_ROI