The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a table Visual that shows Student names and various measures. All I want to do is insert a rank for two of the measurs (RPI and Avg_AtL below).
My closest shot so far is to create a separate table and then, provided I aggregate the field, the RANKX works. However I am unable to bring it back into my original table visual without some sort of filtering taking place as it comes out with '1' for all rows.
I actually get the same result in the right hand visual - a table against my calculated table if I don't aggregate the value with sum or min i.e. if I change it to 'no aggregation, it also comes out as '1' for all rows.
I've been ages on this and I can't undersatnd why something so simple for my customer to ask for - the ranking of a calculation - seems so beyond me.
Please can someone help!
Thank you. @lbendlin
File Link = https://drive.google.com/file/d/1DCKA_-PWgz8ES6YEtlD8XgZqWdz1OxUo/view?usp=sharing
I've anonymised and downscaled my data to just show the problem I am seeking to solve.
You'll see all the measures showing in a table that my customers like. I just need to add a rank of Avg_AtL.
Later I'll probably need to contextualise the rank with the number in the cohort but that should be an easy card visual. I'm guessing that they may eventually want two rank columns given that they will 'slice' by form group (from another table) - the cohort rank and the group rank.
Anyway, I'm getting ahead of myself - just undersatnding what's required to get a rank of the Avg_AtL measure would probably see me OK for now.
Thanks again for taking the time.
PS I've left my desparate attempts thus far ("aa" prefixed tables and measures) in there so that you can see what I've tried thus far.
Your source data is in need of some gentle unpivoting. Can you please post the Input.xlsx file?
please tag me when you post the sample data.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
Thank you. I've read this and I'm reasonably confident that it's not anything to do with column sorting. It's to do with the filter context, which is giving me just one row. This is a super common thing queried when ranking a column and the general soultion is to add 'ALL' so that RANKX is iterating all rows and not being constarined to the row in the table visual. I need to somehow force the measure to be evaluated for every row.
There's surprising little written on RANKXing a measure.
I'll consider stripping down my database to just the issue and anonomysing the data so that I can post a PBIX file but it has got to be something very simple.
If anyone can come up with a generic approach to my problem in the meantime, I'd really appreciate it.
Thanks!
User | Count |
---|---|
10 | |
9 | |
6 | |
6 | |
5 |
User | Count |
---|---|
21 | |
14 | |
14 | |
9 | |
7 |