Create Sample Size in DAX

[KarenFingerhut]

Hi there

Happy Friday everyone 

Can someone help me please? I'm trying to calculate a sample size value from a dataset. For example below

My overall total count is 24074

I want a 95% confidence

margin of error of 3%

I know my end figure should be 1022. (I used survey monkey site to get this figure) But for the life of me I dont know how to replicate this figure in DAX so I don't need to hard code it in and change it every time my main fogure of 24074 increases or decreases.

Done anyone know how to create this i DAX please...... I'm stumped

Many thanks

Karen

[Kedar_Pande]

Here's the DAX for the sample size calculation:

Sample Size =
VAR Population = 24074
VAR Z = 1.96 // 95% confidence
VAR p = 0.5 // proportion
VAR e = 0.03 // margin of error
VAR Numerator = (Z * Z * p * (1 - p)) / (e * e)
VAR Denominator = 1 + ((Z * Z * p * (1 - p)) / (e * e * Population))
RETURN
DIVIDE(Numerator, Denominator)

 

Replace 24074 with COUNTROWS(YourTable) to make it dynamic. This will give you ~1022.

 

💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn: https://www.linkedin.com/in/kedar-pande

@KarenFingerhut

[Shahid12523 · Accepted solution]

Sample Size Required =
VAR N = COUNTROWS( 'YourTable' )
VAR Z = 1.96
VAR p = 0.5
VAR E = 0.03
VAR n0 = ( Z * Z * p * (1 - p ) ) / ( E * E )
VAR nCorr = ( N * n0 ) / ( N + n0 - 1 )
RETURN
ROUNDUP( nCorr, 0 )

[KarenFingerhut]

@Shahid12523 

You are a genius, thank you so much. This is ace

Have a fabulous weekend, you've made me very happy

Kind regards

Karen