Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
rendalignacio
Helper I
Helper I

Confidence Level (95%)

 

Hi There,

 

Can anyone assis me on getting the confidence level of 95% (Standard mean deviation) for data 18.

 

TypeDateData 1Data2Data 3Data 16Data 17Data 18
PC31921701019612029154.36933.103-4.473
PC31921701012581720154.89336.622-5.186
PC31921701012571314154.70134.166-5.881
PC3192170101384310154.2233.497-3.978
PC3192170101383111153.90130.71-5.592
PC31921701025791557153.62928.631-6.025
1 ACCEPTED SOLUTION

@rendalignacio

 

Here the solution is shown...

 

Report.png

 

Per Date.png

 

Per Month.png

 

 

 

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 Part1st Part

 

2nd Part2nd Part

 

3th Part3th Part

 

4th Part4th Part

 

Regards

BILASolution

 

 

 

 

View solution in original post

14 REPLIES 14
BILASolution
Solution Specialist
Solution Specialist

Hi @rendalignacio

 

The picture below shows a summary of stadistic measures...

 

std deviation.png

 

 

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

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

95%.png

 

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

 

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

Hi @rendalignacio

 

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

 

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

 

Regards

BILASolution

Hi BILASolution,

 

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

CL_CalcCL_Calc

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

Thanks

Shishir 

Hi @shishir999

 

Here is the pbix for all the comunity

 

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

Hi,

 

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

 

Thank you

@rendalignacio

 

Here it is...

 

Data.png

 

Report.png

 

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

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

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

Hi,

 

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

 

Thank you

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@rendalignacio

 

Here the solution is shown...

 

Report.png

 

Per Date.png

 

Per Month.png

 

 

 

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 Part1st Part

 

2nd Part2nd Part

 

3th Part3th Part

 

4th Part4th Part

 

Regards

BILASolution

 

 

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.