Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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 |
---|---|
86 | |
82 | |
68 | |
64 | |
55 |
User | Count |
---|---|
120 | |
99 | |
91 | |
83 | |
65 |