Forum Discussion
Dynamic Quartile Ranking - Filter with Multiple Selections
Hi korina
I looked at your file and I see your problem... And what's worse is what you're trying to do is a completely natural way of showing the data!
Unfortunately you won't get around the issue of a calculated column only recalculates on data refresh and not being able to use a measure in the axis of a chart. So can I suggest maybe changing how you want to present the data? I know...not ideal, especially since what you're trying to do makes complete sense!
I created a measures to calculate 25th, 50th, 75th and 99th percentiles and then measures to calculate the headcount that falls into each quartile. Presented with male/female on the axis.
I like your presentation better because it clearly shows the gender pay gap in the data. Let me think some more and maybe I can come up with something? Wish I had a better reply 😕
Hi korina
The real issue is that you're creating this as a calculated column. Calculated columns are only calculated when data is refreshed - and not based on filter context changing. Try redoing your quartile calculation as a measure...you will probably have much more success with it.
BTW: I tried downloading your file, but there's no pbix file available.
Hi littlemojopuppy! Thank you so much for taking the time to reply to me!
Yes, I understand that the calculated column is calculated once and doesn't interact with slicers, that is why I tried moving to a measure. However, measures can't be used in graphs, as they don't take into account all the rest of the information I want.
I checked my file from a different gmail account and I could download it, could you please give it another try and see what I have done? If you download the whole thing, it saves as a pbix file.
Hi korina
I looked at your file and I see your problem... And what's worse is what you're trying to do is a completely natural way of showing the data!
Unfortunately you won't get around the issue of a calculated column only recalculates on data refresh and not being able to use a measure in the axis of a chart. So can I suggest maybe changing how you want to present the data? I know...not ideal, especially since what you're trying to do makes complete sense!
I created a measures to calculate 25th, 50th, 75th and 99th percentiles and then measures to calculate the headcount that falls into each quartile. Presented with male/female on the axis.
I like your presentation better because it clearly shows the gender pay gap in the data. Let me think some more and maybe I can come up with something? Wish I had a better reply 😕
Hi korina
I don't have your pbix with me (I'm at work). But what you can try is to wrap the percentile calculations in a CALCULATE statement and add REMOVEFILTERS([Sex/Gender]). That should take care of it.
