Average for DAX Column Incorrect

[rogerthat]

Hi,

I'm trying to re-create the measure below but in DAX formula so the averge is correct.

This is my measure formula:

_Average Corrected =

CALCULATE(

    AVERAGEX(

        SUMMARIZE( 'Employee Data v2',  'Employee Data v2'[Emp ID], 'Employee Data v2'[Swipe Data v2.Attribute]),

        CALCULATE( AVERAGE('Employee Data v2'[__Avg/Wk Average/Week Normalized]) )

    )

)

This is my DAX column formula:

__Avg/Wk Average/Week Normalized =

CALCULATE(  

    AVERAGE('Employee Data v2'[_Avg/Wk Average Days/Week]),

       ALLEXCEPT('Employee Data v2','Employee Data v2'[Swipe Data v2.Attribute],'Employee Data v2'[Emp ID])

)

And whenever I place the data in a table, my average for _Average Corrected is fine.

However, for __Avg/Wk Average/Week Normalized it is not the exact value. 

Data I'm working with:

Swipe Data v2.Attribute	Emp ID	_Average Corrected	Average of __Avg/Wk Average/Week Normalized
1/1/2023	1	0.5	0.5
1/1/2023	2	0	0
1/1/2023	3	0	0
2/1/2023	1	0	0
2/1/2023	2	0	0
2/1/2023	3	0	0
3/1/2023	1	0	0
3/1/2023	2	0	0
3/1/2023	3	0	0
4/1/2023	1	0	0
4/1/2023	2	0.3	0.3
4/1/2023	3	0	0
5/1/2023	1	0.2	0.2
5/1/2023	2	0	0
5/1/2023	3	0.2	0.2
6/1/2023	1	0.5	0.5
6/1/2023	2	0	0
6/1/2023	3	0	0

[Anonymous]

Hi  @rogerthat ,

Sorry, I can't reproduce your scene, in the data I created, [__Avg/Wk Average/Week Normalized] and [_Average Corrected] appear the same value, there are no two inconsistencies.

Can you show a sample of [_Avg/Wk Average Days/Week] or share a sample pbix after removing sensitive data, we can help you better.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.