topic Re: Looking for a different method to display Percentage viewers retained per Second in DAX Commands and Tips

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

dillennwin — Thu, 19 Aug 2021 13:01:59 GMT

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.

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

Samarth_18 — Thu, 19 Aug 2021 13:36:56 GMT

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

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

dillennwin — Thu, 19 Aug 2021 14:32:20 GMT

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?

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

Samarth_18 — Thu, 19 Aug 2021 14:51:06 GMT

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.

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

dillennwin — Mon, 23 Aug 2021 09:15:19 GMT

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?

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

Samarth_18 — Mon, 23 Aug 2021 11:00:44 GMT

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"

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

dillennwin — Mon, 23 Aug 2021 14:00:22 GMT

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.

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

Samarth_18 — Mon, 23 Aug 2021 14:18:33 GMT

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

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

dillennwin — Tue, 24 Aug 2021 09:13:12 GMT

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

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

Samarth_18 — Tue, 24 Aug 2021 10:27:15 GMT

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"

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

dillennwin — Tue, 24 Aug 2021 10:57:19 GMT

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