Calculating average in rows rather than as column

[Anonymous]

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!

[dax · Accepted solution]

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]

Thanks dax, works a charm

[Greg_Deckler]

Create a disconnected table with your firms and benchmarks and then make the right calculation depending on the category, either a firm or a benchmark. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[Anonymous]

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?