Trying to create Custom Column to Average the Values in Multiple Columns

[dpancratz]

I have a column that may contain zero or multiple values delimited by a semi-colon. 

RATING

5

4

3;4

I would like to average the the ratings in a new custom column. My ideal end-result would be something along the lines of: 

RATING	Count of RATING	AVG of Rating
5	1	5
4	1	4
3;4	2	3.5

Any recommendations? 

[v-shex-msft · Accepted solution]

Hi @dpancratz,

You can follow below steps(Query editor) to achieve your requirement.

Source table:

Add custom column to get the column of subitems.

Count = Table.AddColumn(#"Changed Type", "Count", each if Text.Contains([RATING],";") then List.Count(Text.Split([RATING],";")) else 1)

Add custom column to get the average of subitems.

Average = Table.AddColumn(Count, "Average", each if Text.Contains([RATING],";") then List.Average(List.Transform(Text.Split([RATING],";"), each Number.FromText(_))) else Number.FromText([RATING]))

Test more records:

Full Query:

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\test.xlsx"), null, true),
    Sheet8_Sheet = Source{[Item="Sheet8",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet8_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"RATING", type text}}),
    Count = Table.AddColumn(#"Changed Type", "Count", each if Text.Contains([RATING],";") then 
    List.Count(Text.Split([RATING],";")) else 1),
    Average = Table.AddColumn(Count, "Average", 
    each if Text.Contains([RATING],";") then 
    List.Average(List.Transform(Text.Split([RATING],";"), each Number.FromText(_))) else Number.FromText([RATING]))
in
    Average

Comments.
Text.Split : split the text by ";".
List.Transform : transform a text list to number list.(the prerequisite steps)
List.Average : get the average of the list. (only work on numeric list)

Regards,

Xiaoxin Sheng

[Sean]

After you've split the Column in the Query Editor - select the unique row identifier column (or index column)

still in the Query Editor - Transform Tab - click Unpivot Other Columns - then Home tab - Close and Apply

Then create 2 Measures

Ratings Measure = COUNTROWS ( Table ) and

Avg Rating Measure = DIVIDE ( SUM (Table[Value] ), [Ratings Measure], 0 )

Then here's the Table Visual

[dpancratz]

Here's what I've tried so far: 

1. Split the column [RATING] be deliminator

2. Creating a custom column and trying to figure out the DAX function to get what i want

I'm somewhat new, so I'm quickly runningout of ideas. 

[th3h0bb5]

It's not very pretty (so you might keep hunting) but this worked for me:

• Do your column split in the Data query
• Add an Index column in the Query so that each row as a unique identifier
• Create an 'OverallRank' calculated measure to find the average:
• overallRank = (SUM([Rating.1])+SUM([Rating.2])+SUM([Rating.3]))/(COUNTA([Rating.1])+COUNTA([Rating.2])+COUNTA([Rating.3]))

The end result looks like this: