We're giving away 30 tickets for FREE! Share your story, your vision, or your hustle and tell us why YOU deserve a ticket.
Apply nowWin a FREE 3 Day Ticket to FabCon Vienna. Apply now
X(Calculated Coulmn) | CDF(Measure) |
100 | .11 |
150 | .24 |
200 | .30 |
I have a CDF probaility table created with x value as caclulated column and CDF value f(x) with a measure. Now i hope to get quartiles of the CDF . Like i want the value of x where CDF is 0.25. How can i do that? Kindly help
Solved! Go to Solution.
// There must be a table with a column
// from which you can choose the quantile
// percentage, like .25. The table that
// stores x and cdf(x) must start with x
// for which cdf(x) = 0 and end with x
// for which cdf(x) = 1. Here's a measure
// that returns the value of the quantile
// (linear approximation). Q table stores
// the percentages you'd like to calculate
// quantiles for. CDF table stores (x, cdf(x)).
[Quantile] =
var __percentage = SELECTEDVALUE( Q[Level], 2 )
var __cdfLessThanOrEqualToPercentage =
TOPN(1
FILTER(
ALL( CDF ),
CDF[cdf] <= __percentage
),
CDF[cdf],
DESC
)
var __cdfGreaterThanPercentage =
TOPN(1,
FILTER(
ALL( CDF ),
CDF[cdf] > __percentage,
),
CDF[cdf],
ASC
)
var __quantileEstimate =
if(
NOT ISEMPTY( __cdfGreaterThanPercentage ),
var x1 =
MINX(
__cdfLessThanOrEqualToPercentage,
CDF[x]
)
var y1 =
MINX(
__cdfLessThanOrEqualToPercentage,
CDF[cdf]
)
var x2 =
MINX(
__cdfGreaterThanPercentage,
CDF[x]
)
var y2 =
MINX(
__cdfGreaterThanPercentage,
CDF[cdf]
)
return
divide( x2 - x1, y2 - y1)
* (__percentage - y1)
+ x1
)
return
__quantileEstimate
// There must be a table with a column
// from which you can choose the quantile
// percentage, like .25. The table that
// stores x and cdf(x) must start with x
// for which cdf(x) = 0 and end with x
// for which cdf(x) = 1. Here's a measure
// that returns the value of the quantile
// (linear approximation). Q table stores
// the percentages you'd like to calculate
// quantiles for. CDF table stores (x, cdf(x)).
[Quantile] =
var __percentage = SELECTEDVALUE( Q[Level], 2 )
var __cdfLessThanOrEqualToPercentage =
TOPN(1
FILTER(
ALL( CDF ),
CDF[cdf] <= __percentage
),
CDF[cdf],
DESC
)
var __cdfGreaterThanPercentage =
TOPN(1,
FILTER(
ALL( CDF ),
CDF[cdf] > __percentage,
),
CDF[cdf],
ASC
)
var __quantileEstimate =
if(
NOT ISEMPTY( __cdfGreaterThanPercentage ),
var x1 =
MINX(
__cdfLessThanOrEqualToPercentage,
CDF[x]
)
var y1 =
MINX(
__cdfLessThanOrEqualToPercentage,
CDF[cdf]
)
var x2 =
MINX(
__cdfGreaterThanPercentage,
CDF[x]
)
var y2 =
MINX(
__cdfGreaterThanPercentage,
CDF[cdf]
)
return
divide( x2 - x1, y2 - y1)
* (__percentage - y1)
+ x1
)
return
__quantileEstimate
Hello, thank you for your help with this! In my model, only x is a column in a table. The CDF values are a measure which i was not able to convert into a calculated column. Therefore i am not able to implement your DAX in my model as it uses CDF with functions like ALL which i cannot use as my CDF is a measure. How can i find my quantiles?
Hello, made some updates and the Dax works!
Thank you so much for helping me with this. I probably could not have figured it out as am just getting started with Dax.
Thank you!
You will have to write another measure that does the quartile computation independently of your first measure.
User | Count |
---|---|
13 | |
12 | |
8 | |
8 | |
6 |
User | Count |
---|---|
28 | |
20 | |
14 | |
11 | |
5 |