Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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"
)
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"
)
User | Count |
---|---|
98 | |
89 | |
82 | |
71 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |