topic Re: How to rank two fields using rankx in DAX Commands and Tips

How to rank two fields using rankx

Pranali_R — Fri, 23 Dec 2022 08:57:11 GMT

I am trying to create a visual like this.

I am unable to rank transaction count and amount at the same time. When users input how many top N entries they want to see, the count and amount should be ranked dynamically

Number of entries =

VAR SelectedTop = SELECTEDVALUE('TopN'[TopN ])

RETURN

SWITCH(TRUE(),

RANKX (

ALLSELECTED( Query[CPO NAME] ),

[Count]

)

<= SelectedTop,[Count]

)

Re: How to rank two fields using rankx

Mahesh0016 — Fri, 23 Dec 2022 10:06:33 GMT

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: How to rank two fields using rankx

v-zhangti — Wed, 28 Dec 2022 06:31:35 GMT

Hi, Pranali_R

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to rank two fields using rankx

Pranali_R — Mon, 09 Jan 2023 09:21:35 GMT

v-zhangti

Mahesh0016

Here is the sample data. I want to create a topN slicer based on the total of transaction amount and Transaction Count. Based on the number of entries selected it should show me only those number of records.

Example if I Select Number of Entries as 1,

The visual should display C1 with count as 5 and 15100

Car ID	Car Park ID	transaction amount	Total of transaction amount	Transaction count
C1	C101	1000	15100	5
C1	C102	1500
C1	C103	2500
C1	C104	2300
C1	C105	7800
C2	C201	453	703	2
C2	C202	250
C3	C203	100	550	3
C3	C204	450
C3	C205	2400
C4	C110	11000	11000	1
C5	C234	1000	1000	1

Input :

Output :

Re: How to rank two fields using rankx

Mahesh0016 — Mon, 09 Jan 2023 09:46:03 GMT

hi Pranali_R can you eaxplain about entries in slicer you use.

Re: How to rank two fields using rankx

Pranali_R — Tue, 10 Jan 2023 08:22:26 GMT

Mahesh0016

I am using the Top N Dynamic What if Parameter approach to rank and filter the entries.

But using this.. I am able to just filter on one attribute ..either transaction count or transaction amount.

Number of entries =

VAR SelectedTop = SELECTEDVALUE('TopN'[TopN ])

RETURN

SWITCH(TRUE(),

RANKX (

ALLSELECTED( Query[CPO NAME] ),

[Count]

)

<= SelectedTop,[Count]

)