Dynamic Ranking

[sprakash1192]

Hello All,

I have a dynamic problem to solve where I would like to rank my data dynamically on different measures and dimensions using slicers.

I have daily sales data which looks like this:

Customer	Region	Date	Cost	Sales	InvOH
A	DFW	2/13/2021	42	82	18
B	AUS	2/13/2021	72	174	14
C	TYL	2/13/2021	92	74	23
D	WAX	2/13/2021	85	51	27
E	HOU	2/13/2021	41	104	11
F	LOS	2/13/2021	76	129	23
G	SCHL	2/13/2021	22	75	14
H	NSF	2/13/2021	20	170	20
I	DFW	2/13/2021	19	161	27
J	AUS	2/13/2021	48	139	12
K	TYL	2/13/2021	91	128	17
L	WAX	2/13/2021	44	159	20
M	HOU	2/13/2021	100	53	16
N	LOS	2/13/2021	30	157	19
O	SCHL	2/13/2021	18	158	24
P	NSF	2/13/2021	56	90	22
Q	DFW	2/13/2021	37	190	22
R	AUS	2/13/2021	10	145	24
S	TYL	2/13/2021	78	169	22
T	WAX	2/13/2021	72	173	14
U	HOU	2/13/2021	55	118	17
V	LOS	2/13/2021	90	151	17
W	SCHL	2/13/2021	38	80	11
X	NSF	2/13/2021	48	87	15
Y	DFW	2/13/2021	44	82	17
Z	AUS	2/13/2021	29	152	16

I want to dynamically rank my stores or my region by total sales or total cost or total InvOH. I want to have slicer where I can select my dimension - stores / region and then select rack by sales / cost / Inv OH and my rank table displays data based on my selection.

How do I achieve that? Please let me know if this does not make sense.

[jameszhang0805 · Accepted solution]

Just one kind of solution, create an independent table and then create a dynamic measure by switch function.

Dynamic Ranking.pbix 

[Hugh-Guan]

Hi @jameszhang0805 ,

Sorry, the statement you shared is overdue.

I have the same question, how do you create a Rank calculation column or metric?

[Ischaq]

how you create the rank calculation?

[stevedep]

Hi,

You could unpivot and work with a rank formula that works with allselected. Please see below and attached. 

Hope it helps.

Kind regards, Steve. 

 

 

[amitchandak]

@sprakash1192 , Approch seems very similar to measure slicer. But might need use calculation group

measure slicer
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...

calculation groups

https://www.youtube.com/watch?v=vlnx7QUVYME //measure slicer

https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale