Forum Discussion
Calculate unique viewership duration
You should be able to use a simple SUMX measure to get your result like this (replace "Data" with your actual table name)
Total Duration Viewed = SUMX(Data, Data[Stop Index (secs)] - Data[Start Index (secs)])
You can use the above in a table visual with Customer and/or Video Name (or just as a card visual).
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
mahoneypat how do i ensure i dont include the overlap in duration?
Did you try the SUMX measure? It calculates the difference on each row and then sums up the results, so should avoid overlapping values.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
mahoneypathowever there will be overlap in duration between each row.
I thought you wanted to make sure overlapping views were included not excluded. Here is a different measure that will calculate the overall duration watched for each customer and video combination. Note this works with your example data, but if you have a customer that watched the first 10 sec and last 10 sec of a video, it would calculate the whole video duration. A more complex expression would be needed to avoid that. I didn't have time to do that now, but may later. Perhaps the below approach will be enough, or get you started.
NewMeasure =
SUMX (
SUMMARIZE ( Table, Table[Customer ID], Table[Video Name] ),
CALCULATE ( MAX ( Table[Stop Index] ) - MIN ( Table[Start Index] ) )
)If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat