cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
Community Champion

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],
#"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"}}),
(
#"Renamed Columns",
"AverageMonth",
(row)=> List.Average(Record.FieldValues(Record.SelectFields(row, List.Select(Table.ColumnNames(#"Renamed Columns"), each Text.Contains(_,"Video")))))
)
in

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

10 REPLIES 10
Frequent Visitor

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?

Community Champion

@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.

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Frequent Visitor

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?

Community Champion

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Frequent Visitor

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.

Community Champion

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Community Champion

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Frequent Visitor
Community Champion

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],
#"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"}}),
(
#"Renamed Columns",
"AverageMonth",
(row)=> List.Average(Record.FieldValues(Record.SelectFields(row, List.Select(Table.ColumnNames(#"Renamed Columns"), each Text.Contains(_,"Video")))))
)
in

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.