RANKX Column/ Measure - Rank to change dynamically when filter

Question

Hi,I am trying to derive dynamic rank for each department by count of department which should generate rank when filters are applied (Company Cluster, Type, Code). The summaized data looks like this,&nbsp;DeptRankAsset Class 1Asset Class 2Asset Class 3Asset Class 4Asset Class 5Grand TotalDept 71446594131&nbsp;334Dept 11284186058&nbsp;220Dept 12348317956&nbsp;214Dept 4478345732&nbsp;201Dept 2564873415&nbsp;200Dept 3679214632&nbsp;178Dept 9735871434&nbsp;170Dept 8858124832&nbsp;150Dept 194112354611145Dept 510235446138144Dept 101140176322&nbsp;142Dept 61229193439&nbsp;121&nbsp;I used both Measure and Column&nbsp;&nbsp;Measure =&nbsp;RANKX(ALL(Sheet1[Department])| CALCULATE(COUNT(Sheet1[Department]))| |DESC |Dense)&nbsp;Column =RANKX(Sheet1|CALCULATE(COUNT(Sheet1[Department])|ALLEXCEPT(Sheet1|Sheet1[Department]))| |DESC| Dense)&nbsp;Measure is generating dynamic rank but I require help to create a DAX to store the value that gets ranked 1st, 2nd, etc. For Instance, 'Dept 11' should be fetched as the department coming second and this should change dynamically when the filters are applied.&nbsp;&nbsp;Column is kind off static and does not change as per the filters.&nbsp;&nbsp;Kindly provide your suggestions! Thanks!&nbsp;

parry2k · Answer

Anonymous&nbsp;check these posts on Rank
&nbsp;
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columnshttps://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measureshttps://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
&nbsp;

I would ❤&nbsp;Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

anonymous · Answer

Hi parry2k&nbsp;, thanks for sharing the references, but I want to get the department that is ranked first, second up-to fourth rank as I wish to filter those departments for creating other charts.&nbsp;Thanks..

Forum Discussion

RANKX Column/ Measure - Rank to change dynamically when filter

2 Replies

Related content

Dynamic measure filtering

Dynamic Column headers

Dynamic Measure developement

Measure two columns

Dynamic Column Name

Dept	Rank	Asset Class 1	Asset Class 2	Asset Class 3	Asset Class 4	Asset Class 5	Grand Total
Dept 7	1	44	65	94	131		334
Dept 11	2	84	18	60	58		220
Dept 12	3	48	31	79	56		214
Dept 4	4	78	34	57	32		201
Dept 2	5	64	87	34	15		200
Dept 3	6	79	21	46	32		178
Dept 9	7	35	87	14	34		170
Dept 8	8	58	12	48	32		150
Dept 1	9	41	12	35	46	11	145
Dept 5	10	23	54	46	13	8	144
Dept 10	11	40	17	63	22		142
Dept 6	12	29	19	34	39		121