Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have these three columns in my table.
Now I want to show top 5 based on the maximum hours.
I have applied following formula
RANKX(ALLSELECTED('Table'[Name]),CALCULATE(SUM('Table'[Hours])))
But I have a year slicer too, when I apply the slicer this rank go off…
Meaning this is ranking all despite of slicer, so when I select for example 2015 then rank could be 1,2,3,6 (4,5 missing i.e. rank number in between are missing)
Am I doing any logical mistake here, please advice
This is not the real data, this is just for understanding.
Name | Hours | year |
A | 13 | 2015 |
B | 45 | 2015 |
C | 23 | 2015 |
D | 12 | 2015 |
E | 34 | 2015 |
F | 23 | 2015 |
G | 65 | 2015 |
H | 34 | 2015 |
I | 32 | 2015 |
A | 23 | 2016 |
B | 24 | 2016 |
C | 54 | 2016 |
D | 34 | 2016 |
E | 64 | 2016 |
F | 31 | 2016 |
G | 12 | 2016 |
H | 18 | 2016 |
I | 38 | 2016 |
I suggest you extend your data model into a star schema. Read my article here http://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/
Then create a Year table and a Name table. and put the columns from those table in your visual.
then write this formula
RANKX(ALL(NameTable),CALCULATE(SUM('Table'[Hours])))
Alternatively you could just use this,
RANKX(ALLEXCEPT('Table','Table'[Year]),CALCULATE(SUM('Table'[Hours])))
but that would miss a learning opportunity 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |