Random pick up of single column from a table depending on ranking (RANKX) and multiple slicers

[Anonymous]

Hi All

I am currently trying to generate random selected of names based on a RANKX dax calculating the higher net profit company, depending on "Size" and "Location" slicer. I would ideally like to show only 2 "Blind names" in a little table within the top 5 of my RANKX based on the following table: 

Blind name	Real name	Net profit	Size	Location
A	AAAA	10	Small	Germany
B	BBB	11	Mid	England
C	CCC	12	Mid	Netherlands
D	DDD	13	Mid	Germany
E	EEE	14	Large	England
F	FFF	15	Large	Netherlands

I am currently using the following DAX measure to calculate the ranking dynamically: 

RANKX(

ALLSELECTED(Data_Benchmark),

CALCULATE(SUM('Data_Benchmark'[Net profit])),

,

DESC,

Dense

)

Does anyone has an idea how I could do it? Any help would be appreciated, many thanks!

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated column.

Rand = INT(RAND()*1000)

2. Create measure.

Rank_Net profit =
RANKX(ALL('Table'),CALCULATE(SUM('Table'[Net profit ])),,ASC,Dense)

Rank_Rand =
RANKX(
    FILTER(
    ALL('Table'),
    [Rank_Net profit]<=5),   
    CALCULATE(SUM('Table'[Rand])),,ASC,Skip)

Flag =
IF(
    [Rank_Net profit]<=5&&[Rank_Rand]<=2,1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

If you need pbix, please click here.

Random pick up of single column from a table depending on ranking (RANKX) and multiple slicers.pbix

Best Regards,

Liu Yang

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

[Anonymous]

Thanks works fine for me

[lbendlin]

You can fudge it somehow with a second measure that runs another "random" RANKX over your top 5 items (by the way you can use TOPN() instead of RANKX() for that).  However, it is not really random, and too random at the same time. So you will have scenarios when more or fewer than your choice of two random accounts will be returned.