Forum Discussion
Confidence Level (95%)
Hi There,
Can anyone assis me on getting the confidence level of 95% (Standard mean deviation) for data 18.
| Type | Date | Data 1 | Data2 | Data 3 | Data 16 | Data 17 | Data 18 |
| PC3192 | 170101 | 961 | 2029 | 15 | 4.369 | 33.103 | -4.473 |
| PC3192 | 170101 | 258 | 1720 | 15 | 4.893 | 36.622 | -5.186 |
| PC3192 | 170101 | 257 | 1314 | 15 | 4.701 | 34.166 | -5.881 |
| PC3192 | 170101 | 384 | 310 | 15 | 4.22 | 33.497 | -3.978 |
| PC3192 | 170101 | 383 | 111 | 15 | 3.901 | 30.71 | -5.592 |
| PC3192 | 170102 | 579 | 1557 | 15 | 3.629 | 28.631 | -6.025 |
Here the solution is shown...
First Picture: Report
Second Picture: Sample Data Per Date
Third Picture: Sample Data Per Month
First, I created a new table (Per Month) based of the original table (Per Date).
On the Ribbon: Modeling Tab --> New Table then...
Per Month = SELECTCOLUMNS('Per Date';"Type";'Per Date'[Type];"Month";FORMAT('Per Date'[Date];"MMMM");"Month Number";FORMAT('Per Date'[Date];"M");"Data 18";'Per Date'[Data 18])Note: To Sort the Month Column, select it and go to the Ribbon: Modeling Tab --> Sort By column (Choose Month Number Column)
Measures:
Per Date Table:
Confidence Level 95% = 1.96
Mean = var ty = FIRSTNONBLANK('Per Date'[Type];1) var da = FIRSTNONBLANK('Per Date'[Date];1) return
CALCULATE(AVERAGE('Per Date'[Data 18]);ALL('Per Date');'Per Date'[Type] = ty;'Per Date'[Date] = da)std Deviation = var ty = FIRSTNONBLANK('Per Date'[Type];1) var da = FIRSTNONBLANK('Per Date'[Date];1) return
CALCULATE(STDEV.P('Per Date'[Data 18]);ALL('Per Date');'Per Date'[Type] = ty;'Per Date'[Date] = da)std error of mean = var ty = FIRSTNONBLANK('Per Date'[Type];1) var da = FIRSTNONBLANK('Per Date'[Date];1) return
CALCULATE(DIVIDE([std Deviation];SQRT(COUNTROWS(ALL('Per Date'))));'Per Date'[Type] = ty;'Per Date'[Date] = da)Lower Limit = [Mean] - [Confidence Level 95%]*[std error of mean]
Upper Limit = [Mean] + [Confidence Level 95%]*[std error of mean]
Out or Within = IF(AND(FIRSTNONBLANK('Per Date'[Data 18];1) >= [Lower Limit];FIRSTNONBLANK('Per Date'[Data 18];1) <= [Upper Limit]);"Within";"Out")
Per Month Table:
Confidence Level 95% 2 = 1.96
Mean 2 = var ty = FIRSTNONBLANK('Per Month'[Type];1) var da = FIRSTNONBLANK('Per Month'[Month];1) return
CALCULATE(AVERAGE('Per Month'[Data 18]);ALL('Per Month');'Per Month'[Type] = ty;'Per Month'[Month] = da)std Deviation 2 = var ty = FIRSTNONBLANK('Per Month'[Type];1) var da = FIRSTNONBLANK('Per Month'[Month];1) return
CALCULATE(STDEV.P('Per Month'[Data 18]);ALL('Per Month');'Per Month'[Type] = ty;'Per Month'[Month] = da)std error of mean 2 = var ty = FIRSTNONBLANK('Per Month'[Type];1) var da = FIRSTNONBLANK('Per Month'[Month];1) return
CALCULATE(DIVIDE([std Deviation 2];SQRT(COUNTROWS(ALL('Per Month'))));'Per Month'[Type] = ty;'Per Month'[Month] = da)Lower Limit 2 = [Mean 2] - [Confidence Level 95% 2]*[std error of mean 2]
Upper Limit 2 = [Mean 2] + [Confidence Level 95% 2]*[std error of mean 2]
Out or Within 2 = IF(AND(FIRSTNONBLANK('Per Month'[Data 18];1) >= [Lower Limit 2];FIRSTNONBLANK('Per Month'[Data 18];1) <= [Upper Limit 2]);"Within";"Out")
For enhancing the solution, you can analyze the number of "within" by type, date , etc. Try this...
1st Part
2nd Part
3th Part
4th Part
Regards
BILASolution
14 Replies
The picture below shows a summary of stadistic measures...
Measures:
Confidence Level 95% = 1,96
Mean = AVERAGE(Table1[Data 18])
std Deviation = STDEV.P(Table1[Data 18])
std error of mean = DIVIDE([std Deviation];SQRT(COUNTROWS(Table1)))
Lower Limit = [Mean] - [Confidence Level 95%]*[std error of mean]
Upper Limit = [Mean] + [Confidence Level 95%]*[std error of mean]
and the next web page contains stadistic theory that could be useful. I hope this answer your question.
Hi,
Thank you for that but lets say i want to remove values that are not within the 95% confidence level from the table.
Is that possible?
Thank you
This time I added a new measure called Out or Within also some modifications
Confidence Level 95% = 1,96
Mean = CALCULATE(AVERAGE(Table1[Data 18]);ALL(Table1[Data 18]))
std Deviation = CALCULATE(STDEV.P(Table1[Data 18]);ALL(Table1[Data 18]))
std error of mean = DIVIDE([std Deviation];SQRT(COUNTROWS(ALL(Table1))))
Lower Limit = [Mean] - [Confidence Level 95%]*[std error of mean]
Upper Limit = [Mean] + [Confidence Level 95%]*[std error of mean]
Out or Within = IF(AND(FIRSTNONBLANK(Table1[Data 18];1) > [Lower Limit];FIRSTNONBLANK(Table1[Data 18];1) < [Upper Limit]);"Within";"Out")
Then, tell me how it was...
