Forum Discussion
Add percentiles using a calculated measure
Hello,
Sorry for the confusion, what I meant to say is that the 4 percentile functions use either a column from a table, or a table itself.
Since I am using a calculated measure to base my percentile on, I have troubles creating my calculated measure.
I have made a simpler version of the file that I can share here.
I would like to be able to calculate the percentile for both the country and the city , but for now I thought about focusing on the country alone.
(for some reasons I cannot link a PBIX file, so for now I will send screen shots)
What's wrong with PERCENTILEX.INC - DAX Guide?
By the way, it's still not clear what you mean by a "percentile measure." A percentile is a 2-variable function , f(x: Multiset(R), y: R) -> R , that depends on: (x) the data set you give it and (y) the threshold you define between 0 and 1. So I'm still unable to determine exactly what you really want. Which exactly percentile are you talking about? For instance, if you want the 25th percentile (the 1st quartile) for cities based on some [M]easure calculated for the cities, then you'd write:
percentilex.inc( summarize( FactTable, Cities[CityID] ), [M], 0.25 )If you want the same but just for countries, then you'd need to change Cities[CityID] to some kind of Country[CountryID]. If you want to have percentiles for different levels of a hierarchy, then you'll have to create a specific measure for this very hierarchy using ISINSCOPE and a proper formula for each level...
What I am trying to do is to have in which percentile all those cities are. For example, if they are in the top 90% it would be 0.9.
Are you trying to create a measure or a calculated column? It's not obvious. By the way, you cannot write a measure that would return just one number for a set of cities to inform you in what percentile they all are. This question is ill-posed. You should really think hard about what it is you want to do and then carefully describe it here.
I also tried to write the measure as you suggested to try it out, but only got the same result as my employmentratio :
I both tried to use the measure, and declare them in variables like in the screen shot
You can't use variables the way you do. Variables in DAX are in fact static. Once calculated, they never change, so your attempt to use a variable in the function the way you try to is futile.