Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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 |
Solved! Go to Solution.
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
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...
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
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_Calc
Could you plz. share the PBIX file @ shishir999_ril@hotmail.com, it is not available on the link.
Thanks
Shishir
Hi,
I cannot download the file. Can you please post the formulas here?
Thank you
Here it is...
First Picture : Sample Data
Second Picture : Report
Measures:
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
- 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
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
User | Count |
---|---|
132 | |
74 | |
70 | |
58 | |
54 |
User | Count |
---|---|
192 | |
96 | |
67 | |
64 | |
54 |