Dynamic Quartile Ranking - Filter with Multiple Selections

[korina]

Hello everyone,

I want to display a quartile calculation from a dataset that is filtered based on a slicer selection. I have tried using a calculated column which works fine when the slicer only has 1 value selected, but when selecting multiple values the quartile column is not recalculated, and so it doesn't work. I have tried using a measure, but I can't figure out how to convert my column code to a working measure code. I have also tried using a calculated table, but again, I can't figure it out.

I have attached my workbook so you can see what I have tried so far, and explore:

https://drive.google.com/file/d/1mcQGeF1otupEZ_kRM_ShPN87ePPG1EQt/view?usp=sharing

Thank you very much in advance for your help.

Korina

PS. @lbendlin , I hope this now works!

[korina]

Hello @littlemojopuppy!

Thanks so much again, for taking the time to work on my workbook and reply to me! I really appreciate it 🙂 As you pointed out, my graph really shows the gender pay gap issue, and that is what I want to highlight. It is really frustrating that PowerBI doesn't allow you to do what I want to do easily, especially since it is literally a click of a button with other Visualisation tools. I will present both our graphs (with all their restrictions), and let's see what happens.

Do you think a calculated table could work? Or does it work like a calculated column? If a calculated table interacts dynamically with filters, and refreshes everytime, then a calculated column within that table might work?

[littlemojopuppy]

Hi @korina calculated tables are just like columns.  They'll only recalc when data is refreshed, not with filter context.

[korina]

Hi @littlemojopuppy! I am working on your solution today, and I think I can make it work, if I create 4 graphs (one for each quartile), use "Gender" as a legend, and place the graphs next to each other.

However, there is a problem. When you add "Gender" into the picture, the Percentile changes for each gender, so it really divides females into four quartiles, and males into four quartiles separately. This is clear in the table you have created as well. What I would like is to use the "Total" percentiles, rather than the ones under the M and F columns. Employees should be distributed along the quartile regardless of their gender.

Is this something that can be calculated?

[littlemojopuppy]

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.

[korina]

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.

[littlemojopuppy · Accepted solution]

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 😕

[littlemojopuppy]

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.

[korina]

Amazing! I was trying to do that on the Headcount calculation rather than the Percentile calculation. Now it is working 😄 Thank you so so much!

[littlemojopuppy]

Hi @korina glad I could help!