Confidence Level (95%)

[rendalignacio]

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

[BILASolution]

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

[rendalignacio]

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

[BILASolution]

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

[rendalignacio]

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

[BILASolution]

Hi @rendalignacio

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

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

Regards

BILASolution

[shishir999]

Hi BILASolution,

I tried the formula with my data, but not sure, if the solution is correct.

CL_Calc

Could you plz. share the PBIX file @ shishir999_ril@hotmail.com, it is not available on the link.

Thanks

Shishir 

[BILASolution]

Hi @shishir999

Here is the pbix for all the comunity

https://1drv.ms/u/s!AuU-Ye8UGM4Rko9nh9Zw3GKDlM-utg

[rendalignacio]

Hi,

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

Thank you

[BILASolution]

@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

[rendalignacio]

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

[BILASolution]

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

[rendalignacio]

Hi,

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

Thank you

[v-jiascu-msft]

Hi @rendalignacio,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[BILASolution · Accepted solution]

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

1st Part

2nd Part

3th Part

4th Part

Regards

BILASolution