Forum Discussion
Add percentiles using a calculated measure
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.
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)
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?
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.