Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

F.DIST

In my recent quest to create or catalog as many DAX equivalents for Excel functions was able to leverage my work on GAMMA to come up with F.DIST.     F.THIS = VAR __x = [x] VAR __Deg1...
Mathematical
Other
MJEnnis's avatar
MJEnnis
Resolver III
2 years ago

This is pretty awesome! I thought it was weird that PowerBI has functions for the Beta distribution, but not the F distribution. I spent a few hours reflecting on how to use the T distribution or Beta distribution functions to calcuate F.INV. I needed it desparately to dynamically calculate confidence intervals to report a measure of interclass correlation. I was just about to give up and write a complaint to Microsoft when I stumbled upon this great post! Thanks a lot, Greg_Deckler! This is not the first time you have helped me through such problems!

  • MJEnnis's avatar
    MJEnnis
    Resolver III
    2 years ago

    Pasting in an example of the F.INV measure being used in a report to calculate the confidence intervals for an interclass correlation coefficient. Pretty cool to get a dynamic calcuation that works with filters!

     

    No filter appliedMultiple filters applied

    • MJEnnis's avatar
      MJEnnis
      Resolver III
      2 years ago

      And here is the code to calculate ICC with interpretation and confidence intervals. 

      Test_Retest_ICC = 

VAR n = COUNTROWS('TEST_RETEST')
VAR nT = n*2

VAR k = 2

VAR AvgT = DIVIDE(SUM('TEST_RETEST'[Test Score]) + SUM('TEST_RETEST'[Retest Score]),nT)

VAR SST = SUMX('TEST_RETEST', ('TEST_RETEST'[Test Score]-AvgT)*('TEST_RETEST'[Test Score]-AvgT)) + SUMX('TEST_RETEST', ('TEST_RETEST'[Retest Score]-AvgT)*('TEST_RETEST'[Retest Score]-AvgT))

VAR SSW = SUMX('TEST_RETEST', 
('TEST_RETEST'[Test Score]-DIVIDE('TEST_RETEST'[Retest Score]+'TEST_RETEST'[Test Score],k)) * 
('TEST_RETEST'[Test Score]-DIVIDE('TEST_RETEST'[Retest Score]+'TEST_RETEST'[Test Score],k)) + 
('TEST_RETEST'[Retest Score]-DIVIDE('TEST_RETEST'[Retest Score]+'TEST_RETEST'[Test Score],k)) * 
('TEST_RETEST'[Retest Score]-DIVIDE('TEST_RETEST'[Retest Score]+'TEST_RETEST'[Test Score],k)))

VAR SSB = SST-SSW

VAR DFW = (n*k)-n
VAR DFB = n-1

VAR MSB=DIVIDE(SSB,DFB)
VAR MSW=DIVIDE(SSW,DFW)

VAR ICC = (MSB-MSW)/(MSB+((k-1)*MSW))

Var CCT = 
SWITCH(TRUE,
       ICC>=-1 &&  ICC<0 ,"Invalid Estimate",
        ICC=0                           ,"No Agreement",
        ICC>0    &&  ICC<0.4   ,"Poor Agreement",
        ICC>=0.4 &&  ICC<0.6   ,"Fair Agreement",
        ICC>=0.6 &&  ICC<0.75   ,"Good Agreement",
        ICC>=0.75 &&  ICC<1   ,"Excellent Agreement",
        ICC=1                           ,"Perfect Agreement"
)

VAR F = MSB/MSW

VAR alpha = 0.05
VAR alpha_Tail = alpha/2

/* The next two variables are taken from the F.INV measure provided by Greg. Ideal to report confidence intervals with the ICC, and you need F.INV to calculate those. This is but just one example of a practical application of Greg's measure. It can also be used for ANOVA stats, for example. Until the PBI developers add F.DIST and F.INV DAX functions, I will be borrowing this measure!  */

VAR FINV_L = BETA.INV(1-alpha_Tail,DFB/2,DFW/2)  * DFW/(DFB*(1-BETA.INV(1-alpha_Tail,DFB/2,DFW/2)))
VAR FINV_U = BETA.INV(1-alpha_Tail,DFW/2,DFB/2)  * DFB/(DFW*(1-BETA.INV(1-alpha_Tail,DFW/2,DFB/2)))

VAR F_L = DIVIDE(F,FINV_L)
VAR F_U = F*FINV_U

VAR LOWER_ = DIVIDE(F_L-1,F_L+k-1)
VAR UPPER_ = DIVIDE(F_U-1,F_U+k-1)


RETURN CCT & ", ICC(1,1)=" & ROUND(ICC,3) & ", 95% CI [" & ROUND(LOWER_,3) & ", " & ROUND(UPPER_,3) &"]" & " (n=" & n & ")"