Add percentiles using a calculated measure

[Anonymous]

Hello everyone, first time posting, I will do my best to make this post right  :

I am stuck right now on a rather complex report I was asked to maintain and update.

The way the data model is made, we have one main table with many subcategories (employment, Births,Deaths etc...), aggregated to the city. 

In this report, I have a calculated measure that finds the Ratio of employment by country by divising the sum of employees by the sum total of people in all the cities within the country.

Once this measure is calculated, I was asked to create the percentile measure to know how each country relate in term of employibility between each other.

Problem, the pre-built functions in power bi all use data that come from a column, and all I have is a calculated measure.

I tried several different solutions, but I never managed to get to the result. 

Little bonus challenge : I have to create the percentile of each of those, for countries and cities using a ratio made with the categories I mentioned.

If what I said is not clear enough, I can try to make a make up simplified file.

Thank you for reading my mail,

Dimitri

[Anonymous]

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)

 

[Anonymous]

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...

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

Ultimately, all I am trying to do is have the corresponding percentile for each row by country and then by city. I am just trying things out and suggesting ideas to get there. 

I would like to avoid calculated columns because to my understanding they are more ressource intensive (the column would be on a large table) especially since I have to make a dozen of percentil measure with data on the same table (I have a dozen categories)

[Anonymous]

OK, but it's still not clear. Let's consider this scenario. You put some cities into a visual (say, table) and have a slicer that only shows cities in 2 different countries. Something like this (let's assume, for the time being, that the names of the cities are unique across the whole data set):

Table 1.

Country | City | EmploymentRatio

--------------------------

US | A | 0.20

US | B | 0.40

US | C | 0.80

UK | D | 0.10

UK | E | 0.50

UK | F | 0.80

Note that I added the column Country but it's only for technical reasons; treat it as if it were not there. In fact the table shows only cities while the countries have been selected somewhere in a slicer put on the canvas.

Let's assume further that there is another slicer somewhere there that has filtered the following cities out of the visual:

US | A1 | 0.15

US | B1 | 0.45

US | C1 | 0.90

UK | D1 | 0.20

UK | E1 | 0.60

UK | F1 | 0.90

Question: What do you expect to see in the rows of the first visual if you put your mentioned measure in there?

[Anonymous]

I went and contacted my client again, because I have noticed there were multiple definitions for percentils. 

He told me he only meant to have each value be divided by the max value in the filtered table. This simplified a lot and I was able to create the measure he was looking for.

Thank you for helping me out, I am still a bit confused as to why I cannot create a temp table in a variable and use this table as the context in an "X" measure (sumx, avgx,...etc) but to my understand the way DAX works, I have to define the table inside of the X function rather than declare it as a variable beforehand.

[Anonymous]

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. 

[Anonymous]

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.

[Anonymous]

"Problem, the pre-built functions in power bi all use data that come from a column, and all I have is a calculated measure."

That's not true, of course. All functions that end in X (SUMX, AVERAGEX...) can calculate the required value using measures. They do iterate over some kind of a value in some kind of a column but this is totally normal and even desired since measures are always calculated against some member(s) in some column(s).