topic Re: Confidence Level (95%) in Desktop

Confidence Level (95%)

rendalignacio — Wed, 11 Oct 2017 01:52:28 GMT

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

Re: Confidence Level (95%)

BILASolution — Wed, 11 Oct 2017 03:04:04 GMT

Hi rendalignacio

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.

Confidence Interval on the Mean

Re: Confidence Level (95%)

rendalignacio — Wed, 11 Oct 2017 05:33:11 GMT

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

Re: Confidence Level (95%)

BILASolution — Wed, 11 Oct 2017 06:08:46 GMT

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...

Re: Confidence Level (95%)

rendalignacio — Fri, 13 Oct 2017 03:27:58 GMT

Hi,

I actually tried the solution and it works but what if i want to have this with different type and different dates then i want to summarize all the "WITHIN" data.

Can you please help me with formula?

Thanks

Re: Confidence Level (95%)

BILASolution — Fri, 13 Oct 2017 04:39:33 GMT

Hi rendalignacio

Here you can find a pbix file with the solution...

https://1drv.ms/f/s!AuU-Ye8UGM4RkopzTsHOeY9bKZoPdA

Regards

BILASolution

Re: Confidence Level (95%)

rendalignacio — Fri, 13 Oct 2017 06:32:19 GMT

Hi,

I cannot download the file. Can you please post the formulas here?

Thank you

Re: Confidence Level (95%)

BILASolution — Fri, 13 Oct 2017 14:56:15 GMT

rendalignacio

Here it is...

First Picture : Sample Data

Second Picture : Report

Measures:

Confidence Level 95% = 1.96
Mean = var ty = FIRSTNONBLANK(Table1[Type];1) var da = FIRSTNONBLANK(Table1[Date];1) return
CALCULATE(AVERAGE(Table1[Data 18]);ALL(Table1);Table1[Type] = ty;Table1[Date] = da)
std Deviation = var ty = FIRSTNONBLANK(Table1[Type];1) var da = FIRSTNONBLANK(Table1[Date];1) return
CALCULATE(STDEV.P(Table1[Data 18]);ALL(Table1);Table1[Type] = ty;Table1[Date] = da)
std error of mean = var ty = FIRSTNONBLANK(Table1[Type];1) var da = FIRSTNONBLANK(Table1[Date];1) return
CALCULATE(DIVIDE([std Deviation];SQRT(COUNTROWS(ALL(Table1))));Table1[Type] = ty;Table1[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(Table1[Data 18];1) >= [Lower Limit];FIRSTNONBLANK(Table1[Data 18];1) <= [Upper Limit]);"Within";"Out")

Regards

BILASolution

Re: Confidence Level (95%)

rendalignacio — Mon, 16 Oct 2017 02:45:22 GMT

Hi,

I was just curious on VAR TY and VAR DA. Can you please explain and also, can you show me how i can calculate the confidence level per month. Letsjust say that i have different ID and multiple dates.

Thank you

Re: Confidence Level (95%)

BILASolution — Mon, 16 Oct 2017 03:18:21 GMT

Hi rendalignacio

- var TY stores the value of the Type Column per each row, and var DA, the value of the Date Column per each row.

- I don't understand, the confidence level mustn't be a constant? (I could be wrong).

Regards

BILASolution

Re: Confidence Level (95%)

rendalignacio — Mon, 16 Oct 2017 07:30:08 GMT

Hi,

I was thinking of calculating the confidence level of 95% per month per id.

Thank you

Re: Confidence Level (95%)

BILASolution — Mon, 16 Oct 2017 18:16:14 GMT

rendalignacio

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...

Regards

BILASolution