Forum Discussion

Flash9883's avatar
Flash9883
New Member
1 year ago
Solved

Data Aggregation issue

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's avatar
    Fowmy
    1 year ago

    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's avatar
    V-yubandi-msft
    1 year ago

    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.

6 Replies

  • techies's avatar
    techies
    Super User
    1 year ago

    Hi Flash9883 please try this

     

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

  • Fowmy's avatar
    Fowmy
    Super User
    1 year ago

    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's avatar
    V-yubandi-msft
    Community Support
    1 year ago

    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.

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Community Support
    1 year ago

    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.