Greg_Deckler
Community Champion
6 years agoQUARTILE
In my recent quest to create or catalog as many DAX equivalents for Excel functions, this was an interesting one because, well, frankly, I can't make heads or tails of how Excel is calculating the 1s...
Hanson97
4 years agoFrequent Visitor
WOW!!!
I was so confused with why finding Quartiles with PERCENTILEX.INC dont match with Box Plot visuals Quartiles
But your solution fixed it..Thanks!!
The only small fix required in your code is you will need to include the median if there are odd sets of data points and exclude them if there are even sets..
QUARTILE =
VAR __Values = SELECTCOLUMNS('Table',"Values",[Column1])
VAR _Count = COUNTROWS(_Values)
VAR _reminder = MOD(_Count,2)
VAR __Quart = MAX('Quartiles'[Quart])
VAR __Median = MEDIANX(__Values,[Values])
VAR __Quartile_Even =
SWITCH(__Quart,
0,MINX(__Values,[Values]),
2,__Median,
4,MAXX(__Values,[Values]),
1,MEDIANX(FILTER(__Values,[Values] < __Median),[Values]),
3,MEDIANX(FILTER(__Values,[Values] > __Median),[Values])
)
VAR __Quartile_Odd =
SWITCH(__Quart,
0,MINX(__Values,[Values]),
2,__Median,
4,MAXX(__Values,[Values]),
1,MEDIANX(FILTER(__Values,[Values] <= __Median),[Values]),
3,MEDIANX(FILTER(__Values,[Values] >= __Median),[Values])
)
RETURN
IF( _reminder = 0,__Quartile_Even, __Quartile_Odd)