Looking for a different method to display Percentage viewers retained per Second

[dillennwin]

For a social media report, I have a visual showing the average percentage viewers retained per second concerning video posts.  

Example:

Seconds Video 1 Video 2 Video 3 Average Percentage Retained

  0            0.95     0.95        0.95            0.95

  1            0.90     0.93        0.87            0.91

  2            0.85     0.90        0.80            0.85

The decimals basically mean that 90%, of the viewers watched 1 second of video 1. I created a calculated column called average percentage retained using the formula: (Average Percentage Video watched per Second = ([Video 1] + [Video 2] + [Video 3])/3).

Since I have to keep changing this every time I make a new video post, I'm looking for some alternatives that work without having to do this.

[dillennwin]

Hello Samarth,

Thank you for your answering my question. However, I have to apologize since I didn't explain it clearly enough.

It's about the formula: (Average Percentage Video watched per Second = ([Video 1] + [Video 2] + [Video 3])/3). 

If I add another video, this means I would have to manually change the formula to (Average Percentage Video watched per Second = ([Video 1] + [Video 2] + [Video 3]+[Video 4])/4).

This is rather tiresome so I was wondering if it was possible to have it done automatically with a correct formula?

[Samarth_18]

@dillennwin Ok got it. You can add a custom column in Power Query with below code. It will take avg of all the column which contains "Video" in name:-

(row)=> List.Average(Record.FieldValues(Record.SelectFields(row, List.Select(Table.ColumnNames(#"Changed Type"), each Text.Contains(_,"Video")))))

Ouput:-

Now click on Close & Apply and change newly created column as percentage type.

[dillennwin]

This is indeed what I'm looking for, thank you very much. However, this is the output that I'm getting:

This is what I used: 

 each (row)=> List.Average(Record.FieldValues(Record.SelectFields(row, List.Select(Table.ColumnNames(#"FB Percentage of Views per second"), each Text.Contains(_,"Video"))))))

The only change I made was in List.Select, to make it refer to how my table is called. Maybe more things need to be modified to get the desired result?

[Samarth_18]

You dont need to make any changes. You can use it as it is:-

(row)=> List.Average(Record.FieldValues(Record.SelectFields(row, List.Select(Table.ColumnNames(#"Changed Type"), each Text.Contains(_,"Video")))))

if it is still not working then you can go to advance editor and replace last two line with below code:-

#"Added Custom" = Table.AddColumn
    (
        #"Changed Type",
        "AverageMonth",
        (row)=> List.Average(Record.FieldValues(Record.SelectFields(row, List.Select(Table.ColumnNames(#"Changed Type"), each Text.Contains(_,"Video")))))
    )
in
   #"Added Custom"

[dillennwin]

I initially tried doing this, however it gave me this error: Expression.Error: The name 'Changed Type' wasn't recognized. Make sure it's spelled correctly.

This is also the reason I changed it, thinking that would solve it. Replacing the last two lines in the advanced editor didn't seem to make a difference.

[Samarth_18]

Could you please share your advance editor code?or your PBIX file after removing sensitive data?

[Samarth_18]

Hi @dillennwin ,

You can directly change your avg column to percentage column from the top ribbon.

or you can create a column with below code:-

Column = 
var _avg = DIVIDE(('Avg'[Video 1]+'Avg'[Video 2]+'Avg'[Video 3]),3)
return FORMAT(_avg,"0.00%")

Thanks,

Samarth

[dillennwin]

Here you go: https://1drv.ms/u/s!Al_2VhmuKdfEfV5Uw3kvu3iz108?e=rSgQMF

[Samarth_18 · Accepted solution]

I am not able to run tranformation since data imported from excel and its available at your desktop but based on understanding you can copy paste below code at advance editor:-

let
    Bron = Excel.Workbook(File.Contents("C:\Users\Dylan\Desktop\Data\Facebook post\Percentage of views per second.xlsx"), null, true),
    #"Percentage of Views per second_Sheet" = Bron{[Item="Percentage of Views per second",Kind="Sheet"]}[Data],
    #"Headers met verhoogd niveau" = Table.PromoteHeaders(#"Percentage of Views per second_Sheet", [PromoteAllScalars=true]),
    #"Type gewijzigd" = Table.TransformColumnTypes(#"Headers met verhoogd niveau",{{"Seconds", Int64.Type}, {"109362120541400_342171220593821", type number}, {"109362120541400_341586157318994", type number}, {"109362120541400_281096060034671", type number}, {"109362120541400_295363821941228", type number}, {"109362120541400_330793401731603", type number}, {"109362120541400_305269554283988", type number}, {"109362120541400_313523823458561", type number}, {"109362120541400_296695761808034", type number}, {"109362120541400_325923882218555", type number}, {"109362120541400_284139049730372", type number}, {"109362120541400_307816697362607", type number}, {"109362120541400_311663670311243", type number}, {"109362120541400_302894911188119", type number}, {"109362120541400_298575068286770", type number}, {"109362120541400_307153587428918", type number}}),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Type gewijzigd", "Duration", each #duration(0,0,0,[Seconds])),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Duration"}),
    #"Renamed Columns" = Table.RenameColumns(#"Aangepaste kolom toegevoegd",{{"109362120541400_342171220593821", "Video 1"}, {"109362120541400_341586157318994", "Video 2"}, {"109362120541400_281096060034671", "Video 3"}, {"109362120541400_295363821941228", "Video 4"}, {"109362120541400_330793401731603", "Video 5"}, {"109362120541400_305269554283988", "Video 6"}, {"109362120541400_313523823458561", "Video 7"}, {"109362120541400_296695761808034", "Video 8"}, {"109362120541400_325923882218555", "Video 9"}, {"109362120541400_284139049730372", "Video 10"}, {"109362120541400_307816697362607", "Video 11"}, {"109362120541400_302894911188119", "Video 13"}, {"109362120541400_311663670311243", "Video 12"}, {"109362120541400_298575068286770", "Video 14"}, {"109362120541400_307153587428918", "Video 15"}}),
    #"Added Custom" = Table.AddColumn
    (
        #"Renamed Columns",
        "AverageMonth",
        (row)=> List.Average(Record.FieldValues(Record.SelectFields(row, List.Select(Table.ColumnNames(#"Renamed Columns"), each Text.Contains(_,"Video")))))
    )
in
   #"Added Custom"

[dillennwin]

Thank you very much, I appreciate you for helping me for the past few days.