Forum Discussion

rendalignacio's avatar
rendalignacio
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's avatar
    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's avatar
BILASolution
Solution Specialist
8 years ago

 

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's avatar
rendalignacio
Helper I
8 years ago

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

    • rendalignacio's avatar
      rendalignacio
      Helper I
      8 years ago

      Hi,

       

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

       

      Thank you

      • BILASolution's avatar
        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

    • shishir999's avatar
      shishir999
      Helper II
      8 years ago

      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 @ [email protected], it is not available on the link.

       

      Thanks

      Shishir