topic Re: RankX Sum of Column and Slicer in DAX Commands and Tips

RankX Sum of Column and Slicer

johnAest — Fri, 03 Dec 2021 17:38:37 GMT

Hello Everybody,

I have a table with transactional records, that table has a counter of 1 per transaction by employee and the measure is an average of 0 and 1 from a different column.

On my power BI I have a slicer that will filter locations.

What i am trying to do is to rankx the average column by employee and filter out if the sum of transacions if is less than 30, whenever I do that the Rank will remain the same after I filter out less than 30

some of the formulas i tried:

RANKX(ALLSELECTED(table[emp_name]), table[avg_metric])

RANKX(ALLSELECTED(table), table[avg_metric])

RANKX(ALLSELECTED(slicer_geo), table[avg_metric])

not all the ranks will be 31 but after I filter out the less than 30 it will hide some their rankings and not generate one for only the selected fields thart met the greater than 30 filter.

Any recommendations?

Re: RankX Sum of Column and Slicer

ValtteriN — Sat, 04 Dec 2021 12:37:01 GMT

Hi John,

So if I undestrood correctly you want to do a RANKX lsiting of employees whose total amount of transactions is more than 30?

My test data (here the goal is to exclude Patrick and John from the ranking):

First I placed if condition on the average metric:

Average metric = if(sum(Ranking[Amount of transactions])>=30,AVERAGE(Ranking[Average]),0)

Then I used this to my advantage in the Ranking measure:

Ranking = if([Average metric]<>0,RANKX(ALLSELECTED(Ranking),Ranking[Average metric]),"

Excluded from ranking

")

End result:

Hopefully this helps!

Re: RankX Sum of Column and Slicer

johnAest — Mon, 06 Dec 2021 16:04:05 GMT

Thanks for taking your time trying to replicate my situation but on my end it doesnt work, do you think that maybe its the format of the values?

So far after using the formula of >30 and when I see the average metric in comparison with an average directly from the column the numbers are totally different, what are you using for summarizing the average metric from that dax formula?

Ranking is also a problem on my end, doesnt show the ranking as expected, what are you using as summary for that?.

The >30 is not removing the employees with less than 30 and I am using the same formula, what else de you think we are missing?

The table you showed it should be the same format that I am using

Average metric is only two values 0 and 1

Counter of transaction is always 1

for example (Im adding index in the screenshot below just for clarification)

Re: RankX Sum of Column and Slicer

ValtteriN — Mon, 06 Dec 2021 19:59:57 GMT

Hi John,

Thanks for the clarification. I now understand the issue better and was able to replicate it more accurately.

First my new test data (my formulas use 5 as a transaction treshold instead of 30 but the logic is the same):

Now the DAX that I used previously didn't work directly so I created an aggreated table as to combat this issue:

temp = SUMMARIZE('Transaction Table2','Transaction Table2'[Employee],"Average Metric T",AVERAGE('Transaction Table2'[Average metric]),"Transactions",COUNT('Transaction Table2'[Transaction]))

My new measures:

Average metric 2 = IF(sum(temp[Transactions])>=5,max(temp[Average Metric T]),"Excluded")

Filter Measure = IF([Average metric 2]="Excluded",0,1)

Ranking From aggregated table =

if(sum(temp[Transactions])>=5,RANKX(allselected(temp),[Average metric 2],,0,Dense),"")

Next step:

Now I added the filter measure to the visualization to exclude employees with less than 5 transactions.

End result:

I believe these steps will solve the issue.

Re: RankX Sum of Column and Slicer

johnAest — Mon, 06 Dec 2021 22:12:06 GMT

You solved this issue, you are the best!!

Just in case in the future sombody comes to this post with the same challenge, in order to create a virtual aggregated table you go to your dashboard, then click modeling and New Table in there you can use the summarize dax formula shared in response and thats it!

really Good job if possible i would give you 1M Kudos !

Thanks so much!