Total Sum of hours not adding same time periods

[Anonymous]

Hi all

I got a bit of struggle on this meassure. I got a table to track recording time of actors in the booth.

There are 2 actors and they can record 3 types of content. Content for A only, content for B only and content for both of them (AB). This third content (AB) can be recorded with both actors together in the booth or they can record it separated.

I want to get the sum of the total hours, but avoiding to sum twice the hours when they attend together (same date and time)

The table looks like this:

The duplicated time I dont want to sum to calculate the total of hours is the orange case (rows 6 and 7), both are together recording. When they record separated despite is the same day but a different hours I want them to be in the total of hours.

I was think I that a SUMX would do the job, but at the time to define the expresion I don't know what to do.

Any clue? thanks!!

[Anonymous]

Well I got to a solution but it doesn't work for totals... I created an ID for the attendances, concatenating the common values, this will generate the same IDs when the actors attend together to record, so the count of ID will determine the number of people in the booth. therefore I got to this DAX expresion:

both adjst = 
 SUMX(
 Data,
 Data[Total hours]/COUNTAX(Data,SELECTEDVALUE(Data[ID]))
 )

Below you can see first the plain data and below it the aggregated table... but the total results in infinite... not sure way... at some point the COUNTAX expresion would return Cero I guess....

any help to fix this??

[lazarus1907]

OK, this is not exactly an elegant solution, and I'm assuming there are two players only, and you made a mistake on the 5th row, where you have A for actor, but AB for content type. So I'm using table A instead:

Now, if you go to modelling and then "Create Table", you can put this code:

Table = 
    VAR sec = GENERATESERIES(0, 86399, 1)
    VAR s = SUMMARIZE(
        'Data',
        'Data'[Time Start], 'Data'[Time End], 'Data'[Date],      
        "Correction",
            VAR d = Data[Date]
            VAR filter_a = FILTER(ALL(Data), Data[Date] = d && Data[Actor] = "A")
            VAR filter_b = FILTER(ALL(Data), Data[Date] = d && Data[Actor] = "B")
            VAR a_min = CALCULATE(MIN(Data[Time Start]), filter_a) *86400
            VAR a_max = CALCULATE(MAX(Data[Time End]), filter_a) * 86400
            VAR b_min = CALCULATE(MIN(Data[Time Start]), filter_b) * 86400
            VAR b_max = CALCULATE(MAX(Data[Time End]), filter_b) * 86400
            VAR filter_1 =  FILTER(sec, [value] < a_min || [Value] > a_max)
            VAR filter_2 =  FILTER(sec, [value] < a_min || [Value] > a_max)
            VAR filter_3 = FILTER(sec, ([value] >= a_min && [Value] < a_max) || ([Value] >= b_min && [Value] < b_max))
            RETURN ROUND(COUNTROWS(filter_3)/3600, 2)
    )
    RETURN s

This new table is B.

Here, dates appear repeated when there is more than one actor per day (both will display the total for the day), but if you use only Date and Correction to create a visual, you get C:

Not a brilliant solution, but it might give you some ideas. At least, when both are working at the same time, their hours are ony counted once. At least the total is 18.

I hope I didn't misunderstand you.

[Anonymous]

Hey @lazarus1907 

I've been trying but not sure why I'm not getting the correction column populated

also I'm wondering If I add other columns It will work... The sample table is a simplified one. The other colummns have the same values as they classify the content in other ways, but I need them to classify the hours.

[Anonymous]

Hi!!

Thanks for the prosousal. Well line 5 is correct as Actor A can attend to record 2 hours of content A only and 2 hours for content AB but doing it alone. It was to show all possible combinations. Anyway I think your solution could work.

I was trying to avoid creating a new table and do it with a DAX meassure, easier to implement in my dataset as it has relation between tablesand other meassuers.

Anyway I'll try it 🙂 thank a lot for your help

[lazarus1907]

Maybe DAX can be used to detect any time range where both overlap on the same day, and subtract it from the total somehow, but even this can be tricky, because you could have [A, 2h, AB and] [A, 2h, AB], which normally would give a total of 4 h, but if both do it at once (e.g. 4/Jan/21), where do you subtract the 2 extra hours?
Person A gets 2 h and B 0? Both get 1h?

[Anonymous]

Well, assign 50% of the time to each one would be a good one as well

[lazarus1907]

Have you only got 2 actors, or more? Because I've got a simple idea that would work fine for just 2 actors.

[Anonymous]

Hi!! @lazarus1907  well there are more actors, but just 2 are involved in recordings together... the rest of them will attenden alone.

[Fowmy]

@Anonymous 

Can you share the expected results based on your sample above and explain how the hours that belong to both actors should be split?

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Anonymous]

@Fowmy Yes sure!!

It would result by adding the hours, and just in the case in orange 10:00 to 12:00 on the 04/01/2021 would be added just 2 hours instead of 4 hours...

hope I explained myself 🙂 thanks!!