Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Quartile Calculation Based on Measure

Hi- I am trying to calculate the quartile boundaries of the aggregated and filtered data set. However, I don't seem to be getting my measures calculation right.  Could someone please help unders...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

     

    Is it what you want?

     

    Quart Summary = 
VAR Sales =
    SUM ( 'Table'[score])
VAR Calls =
   GROUPBY(ALLSELECTED('Table')
      , 'Table'[category]  
      , "total",  SUMX (CURRENTGROUP(), 'Table'[score]))    
     
var q1 = CALCULATE(PERCENTILEX.INC(Calls,[total], 0.25),ALL('Table'))
var q2 = CALCULATE(PERCENTILEX.INC(Calls,[total], 0.5),ALL('Table'))
var q3 = CALCULATE(PERCENTILEX.INC(Calls,[total], 0.75),ALL('Table'))
var q4 = CALCULATE(PERCENTILEX.INC(Calls,[total], 1),ALL('Table'))

RETURN
    // SUMX(calls,[Val])
    SWITCH( 
        TRUE(),
        Sales >= 0 &&  Sales <= q1 ,q1,
        Sales > q1 && Sales <= q2 ,q2,
        Sales > q2 && Sales  <= q3 ,q3,
        "Q4"
    )
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

 

Is it what you want?

 

Quart Summary = 
VAR Sales =
    SUM ( 'Table'[score])
VAR Calls =
   GROUPBY(ALLSELECTED('Table')
      , 'Table'[category]  
      , "total",  SUMX (CURRENTGROUP(), 'Table'[score]))    
     
var q1 = CALCULATE(PERCENTILEX.INC(Calls,[total], 0.25),ALL('Table'))
var q2 = CALCULATE(PERCENTILEX.INC(Calls,[total], 0.5),ALL('Table'))
var q3 = CALCULATE(PERCENTILEX.INC(Calls,[total], 0.75),ALL('Table'))
var q4 = CALCULATE(PERCENTILEX.INC(Calls,[total], 1),ALL('Table'))

RETURN
    // SUMX(calls,[Val])
    SWITCH( 
        TRUE(),
        Sales >= 0 &&  Sales <= q1 ,q1,
        Sales > q1 && Sales <= q2 ,q2,
        Sales > q2 && Sales  <= q3 ,q3,
        "Q4"
    )