Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating average in rows rather than as column

Hi there,

 

I have been searching for a solution to my problem, but unfortunately without luck.

 

My dataset consists of monthly prices for 4 companies. What I would like to do is to calculate the average of the 4 for each month, but NOT in a separate column.

 

Rather, I'd like the "benchmark" to be stored similarly to how the prices of the 4 companies are stored, as I'd like to show the benchmark next to each firm in my graphs, etc.

 

I am using PowerPivot for my further data analysis and to create PivotCharts. Preferably, the benchmark calculation should be done in PowerQuery (I am importing the actual datasets from MS Access), I'll take a PowerPivot solution instead if easier.

 

Any help is greatly appreciated. Unfortunately, I have not been able to enclose a sample workbook, but the image below is a screenshot of the file, and should clarify things.

 

Thanks!

 

Need Help
  • dax's avatar
    dax
    6 years ago

    Hi Anonymous , 

    You could try below M code to see whether it work or not

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcyxDcAwDAPBXVQbsEU5WcZQm/1HiFURApsvvrhzDAtr+nQb9lXccvSLG8iNm5C7bzYv6EIuugC6KpT78AbdkIsuBF0Vyn0t8wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Company = _t, Return = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Company", type text}, {"Return", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Return", each List.Average([Return]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Company", each "Benchmark"),
    #"Appended Query" = Table.Combine({#"Added Custom", #"Changed Type"})
in
    #"Appended Query"

    Best Regards,
    Zoe Zhi

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

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks Greg, I get the idea of using a disconnected table, however, I cannot seem to make it work.

       

      Any suggestions on how to build the measure for my specific case?

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi Anonymous , 

    You could try below M code to see whether it work or not

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcyxDcAwDAPBXVQbsEU5WcZQm/1HiFURApsvvrhzDAtr+nQb9lXccvSLG8iNm5C7bzYv6EIuugC6KpT78AbdkIsuBF0Vyn0t8wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Company = _t, Return = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Company", type text}, {"Return", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Return", each List.Average([Return]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Company", each "Benchmark"),
    #"Appended Query" = Table.Combine({#"Added Custom", #"Changed Type"})
in
    #"Appended Query"

    Best Regards,
    Zoe Zhi

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks dax, works a charm