Forum Discussion

Helper I

8 years ago

Solved

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

Need Help

BILASolution
8 years ago
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

BILASolution

Solution Specialist

8 years ago

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

Helper I

8 years ago

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
Solution Specialist
8 years ago
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
BILASolution
Solution Specialist
8 years ago
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
Helper I
8 years ago
Hi,

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

Thank you
v-jiascu-msft
Microsoft Employee
8 years ago
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

Forum Discussion

Confidence Level (95%)

Related content

Find 95% Confidence Interval for Difference of Means

Power BI Azure Machine Learning Confidence Level

Role Level Security

Page level security

KPI level matrix table