Skip to main content
cancel
Showing results for 
Search instead 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

Reply
dillennwin
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

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"

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

View solution in original post

10 REPLIES 10
dillennwin
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?

 

@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")))))

 

image.png

Ouput:-

image.png

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.
Appreciate your Kudos!!
Connect on Linkedin

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

 

sdasd.jpg

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?

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"

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

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.

error.jpg

 

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.

 

 

 

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Samarth_18
Community Champion
Community Champion

Hi @dillennwin ,

 

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

 

image.png

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%")

 

image.png

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.
Appreciate your Kudos!!
Connect on Linkedin

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"

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors