Help with Average Comparison

[markGti]

Hi All

SiteName	Distinct Count of Item	Average Under/Over
A
B	1	-95
C	11	-85
D	7	-89
E	168	72
F	222	126
G	8	-88
H
I
J
K	96	0
Sum of count distinct	96
Average	73.29

Trying to figure out how to calculate the Average Under/Over - need this to determine how far above/below the average each site is, and then use this for condtional fomartting.

Note: The disticnt count of items is calculated in the matrix, so not sure how to refrence that. Do I need to recalculate it?

I tried this

CALCULATE (

    AVERAGE (DISTINCTCOUNT( 'All Sites'[Item])),

    ALLSELECTED ()

)

but get the error: The AVERAGE function only accepts a column reference as an argument.

Can anyone point me in the right direction with the correct DAX formula?

Thanks

Mark

[v-zhangti · Accepted solution]

Hi, @markGti 

I simulated the data, assuming [Distinct Count of Item] is a measure.

Measure:

Average = AVERAGEX(ALL('Table'),[Distinct Count of Item])

One question, isn't [Average Under/Over] a comparison of calculations to averages? Where did 96 come from?

Average Under/Over = IF([Distinct Count of Item]<>BLANK(),[Distinct Count of Item]-[Average])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[markGti]

Hi

Thanks for reply

Yes that is the result expected. (6 is count distinct for Site K. THere is one slign complication, the Distint Count of Item is absed on a filter (flag = 1)

[v-zhangti]

Hi, @markGti 

Has your problem been solved, if so, please consider Accept a correct reply as the solution to help others find it.

Best Regards