topic Re: Data Aggregation issue in DAX Commands and Tips

Data Aggregation issue

Flash9883 — Sat, 31 May 2025 19:29:06 GMT

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]

)

Re: Data Aggregation issue

techies — Sun, 01 Jun 2025 06:38:03 GMT

Hi Flash9883 please try this

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

Re: Data Aggregation issue

Fowmy — Sun, 01 Jun 2025 06:38:36 GMT

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] )

Re: Data Aggregation issue

Flash9883 — Sun, 01 Jun 2025 14:41:37 GMT

Thanks for the assist this solution worked.

Re: Data Aggregation issue

Flash9883 — Sun, 01 Jun 2025 14:43:01 GMT

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

Re: Data Aggregation issue

V-yubandi-msft — Mon, 02 Jun 2025 05:42:06 GMT

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.

Re: Data Aggregation issue

V-yubandi-msft — Thu, 05 Jun 2025 12:46:13 GMT

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.