Incorrect Duration Calculation Due to GROUP and TEAM Associations in Power BI

Hi manoj_0911 

It is better to state your problem in clear and concise manner, this way it would be helpful to understand easily. What is the problem and what would be your desired output , if prossible , showing dummy output.

 

However, what I understood that, you want to evaluate duration, but want to consider the impact of both team and group while consider removing duplicates. You could try and update your code with below and check result that it evaluates correctly.

 

 

Available_Seconds = 

DIVIDE(
    SUM(AGENT_STATUS_SUBHOUR_V[AVAILABLE]),
    DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[GROUP_NAME]) * DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[TEAM]),
    0
)

 

 

 

AVAILABLE DURATION IN DETAIL TABLE = 
VAR RowLevel_Available =
    DIVIDE(
        SUM(AGENT_STATUS_SUBHOUR_V[AVAILABLE]),
        DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[GROUP_NAME]) * DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[TEAM_NAME]),
        0
    )

VAR TotalLevel_Available =
    SUMX(
        SUMMARIZE(
            AGENT_STATUS_SUBHOUR_V,
            AGENT_STATUS_SUBHOUR_V[USER_ID], -- Calculate per agent
            "AgentAvailable", SUM(AGENT_STATUS_SUBHOUR_V[AVAILABLE]),
            "AgentGroupCount", DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[GROUP_NAME]),
            "AgentTeamCount", DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[TEAM_NAME])
        ),
        DIVIDE([AgentAvailable], [AgentGroupCount] * [AgentTeamCount], 0)
    )

VAR FinalSeconds =
    IF(
        ISINSCOPE(AGENT_STATUS_SUBHOUR_V[USER_ID]),
        RowLevel_Available, -- Use the row-level calculation when in scope
        TotalLevel_Available -- Use the total calculation when out of scope
    )

-- Convert Total Seconds to DD:HH:MM:SS Format
VAR Days = INT(FinalSeconds / 86400)
VAR Hours = INT(MOD(FinalSeconds, 86400) / 3600)
VAR Minutes = INT(MOD(FinalSeconds, 3600) / 60)
VAR Seconds = MOD(FinalSeconds, 60)

RETURN
    IF(
        ISBLANK(FinalSeconds),
        BLANK(),
        FORMAT(Days, "0") & ":" & FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00")
    )

AVG AVAILABLE TIME IN CARDS = 
VAR __total_seconds = SUM(AGENT_STATUS_SUBHOUR_V[AVAILABLE]) // Sum of available seconds
VAR __distinct_groups = DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[GROUP_NAME]) // Distinct group count
VAR __distinct_teams = DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[TEAM_NAME]) // Distinct team count
VAR __average_seconds = DIVIDE(__total_seconds, __distinct_groups * __distinct_teams, 0) // Divide total seconds by distinct group and team count

// Convert the result (average seconds) into DD:HH:MM:SS format
VAR __days = INT(__average_seconds / 86400) // Calculate days
VAR __hours = INT(MOD(__average_seconds, 86400) / 3600) // Calculate remaining hours
VAR __minutes = INT(MOD(__average_seconds, 3600) / 60) // Calculate remaining minutes
VAR __seconds = MOD(__average_seconds, 60) // Calculate remaining seconds

// Format the result into DD:HH:MM:SS
VAR __result = FORMAT(__days, "0") & ":" & FORMAT(__hours, "00") & ":" & FORMAT(__minutes, "00") & ":" & FORMAT(__seconds, "00")

// Return the formatted result or BLANK if no data
RETURN
    IF(ISBLANK(__average_seconds), BLANK(), __result)

 

 

 

By multiplying DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[GROUP_NAME]) * DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[TEAM]), you are accounting for the combined distinct associations of a user with both groups and teams. This ensures that the total duration is divided by the correct number of distinct combinations of groups and teams, preventing overcounting.

 

 

Hope this helps!

If this solved your problem, please accept it as a solution and a kudos!!

 

Best Regards,
Shahariar Hafiz