Data Aggregation issue

[Flash9883]

Good afternoon,

I have a data set that row calculations are correct but column total is not. How can i fix the aggregation.

The underlined 22 is suppose to be a sum. The issues that could be happenning is im filtering by a REG and WRKHM on the rpt date column. if payroll changes occur then multiples might occur on same day. The goal is to figure out how many days an employee has worked in a given time period.

This is the formula im using to get the result in the picture.

SUMX(

    SUMMARIZE(

        FILTER(TRC_Table, TRC_Table[TRC] IN {"REG", "WRKHM"}),

        TRC_Table[Date],

        "UniqueCount", DISTINCTCOUNT(TRC_Table[Date])

    ),

    [UniqueCount]

)

[Fowmy · Accepted solution]

@Flash9883 

Based on the limited information you have shared here, I guess you need to include the employee number in the logic as each row belongs to an employee and you need to het the aggregate in the same manner, try this, replace the employee number in the with your correct column:

SUMX (
    SUMMARIZE (
        FILTER ( TRC_Table, TRC_Table[TRC] IN { "REG", "WRKHM" } ),
        TRC_Table[EmployeeNo], 
        TRC_Table[Date],
        "UniqueCount", DISTINCTCOUNT ( TRC_Table[Date] )
    ),
    [UniqueCount]
)

[V-yubandi-msft · Accepted solution]

Hi @Flash9883 ,

Please consider marking it as the Accepted Solution if it helped resolve your issue. It will be helpful for others in the community who might be facing a similar problem.

Thank you for your understanding.

[V-yubandi-msft]

Hi @Flash9883 ,

If your issue is resolved, it would be great if you could mark the response as the Accepted Solution. This helps others in the community find answers more easily when they face similar challenges.

We really appreciate @Fowmy  & @techies  your clear explanation and the valuable contributions from community members.

If you have more questions or need further support, don’t hesitate to post again the community is always here to help.

Yugandhar

Community Support Team.

[Flash9883]

This solution worked as well. Thanks to both of you.

[Flash9883]

Thanks for the assist this solution worked.

[Fowmy · Accepted solution]

@Flash9883 

Based on the limited information you have shared here, I guess you need to include the employee number in the logic as each row belongs to an employee and you need to het the aggregate in the same manner, try this, replace the employee number in the with your correct column:

SUMX (
    SUMMARIZE (
        FILTER ( TRC_Table, TRC_Table[TRC] IN { "REG", "WRKHM" } ),
        TRC_Table[EmployeeNo], 
        TRC_Table[Date],
        "UniqueCount", DISTINCTCOUNT ( TRC_Table[Date] )
    ),
    [UniqueCount]
)

[techies]

Hi @Flash9883 please try this

Days Worked =
SUMX(
VALUES(TRC_Table[EmployeeID]),
CALCULATE(
DISTINCTCOUNT(TRC_Table[Date]),
TRC_Table[TRC] IN {"REG", "WRKHM"}
)
)