Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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

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: 

RATINGCount of RATINGAVG of Rating
515
414
3;423.5

 

Any recommendations? 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Anonymous,

     

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Anonymous,

     

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      th3h0bb5
      Icon for Resolver II rankResolver II
      9 years ago

      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:

       

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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