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