Quartile Calculation Based on Measure

[andy_pat]

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 understand how to correct either of these measures, so they calculate correct quartiles based on filters.
Test file attached here 

Expected result based on excel PERCENTILE.INC calculation of 
2

5

4

7

3.5	4.5	5.5	7
Q1	Q2	Q3	Q4

but I get the following both of which are incorrect:

category	Total Score	Quartile Median	Quart using Summary
a	2	2	2
b	5	5	5
d	4	5	4
c	7	Q4	7

Measure code: 

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

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

Quartile Median = 
VAR Sales =
    SUM ( 'Table'[score] )
VAR List =
    GROUPBY (
        ALL ( 'Table' ),
        // 'Table'[category],'Table'[Zone],
        'Table'[category],
        "Val", SUMX ( CURRENTGROUP (), [score] )
    )
VAR Pct50 =
    MEDIANX ( List, [Val] )
VAR Pct75 =
    MEDIANX ( FILTER ( List, [Val] >= Pct50 ), [Val] )
VAR Pct25 =
    MEDIANX ( FILTER ( List, [Val] < Pct50 ), [Val] )
RETURN
if(Sales <> BLANK(),
    SWITCH( 
        TRUE(),
        Sales >= 0 && Sales <= Pct25 ,Pct25,
        Sales > Pct25 && Sales <= Pct50 ,Pct50,
        Sales > Pct50 && Sales  <= Pct75, Pct75,
        "Q4"
    ))

Thanks in advance!
Andy

[Vera_33 · Accepted solution]

Hi @andy_pat 

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"
    )