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