Rand() recalculated within TOPN

[Anonymous]

The data table used is below 

     

I have a slicer  nValue, with values from 1 to 20.

Case 1 (working as expected):
Based on the slicer selection, the data table should show the top  products based on Index values.

Measures used:

1.  TotalSales = SUM('Table'[Amount])

2.  top_Products =  //sorts the products based on the top N Index values

         CALCULATE (

                  'Table'[TotalSales],

                  KEEPFILTERS ( TOPN ( nValue[nValue Value], ALLSELECTED ( 'Table' ), 'Table'[Index], DESC ) )

                           )

               ) 

 

      Fig: table sorted correctly based on top 2  Index values

Case 2 (Not working):
Based on the slicer selection, the data table should sort the Products based on the top N random values.
However, the results don't come as expected. 

1. Random = RAND()

2. Top_Random = //should sort the products based on the top N rand() values

CALCULATE (

            ' Table'[TotalSales],

             KEEPFILTERS ( TOPN ( nValue[nValue Value], ALLSELECTED ( 'Table' ), RAND(), DESC ) )

                 )

          )

Here, table gets sorted wrongly - Instead of showing value for top 3 Random values, it is showing 2 other rows,  ie; sorting doesn't consider the rand() values.

Will the value of rand() get changed for  each iteration of TOPN?

Is something happening to the rand() inside the TOPN function, that makes it impossible to sort the values?

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Measure Random and Measure Top_Random perform different random number calculations. The random number generated by Measure op_Random is not the same as the random number generated by Measure Random.

And due to the characteristics of the TOPN function, the returned result may be more than N rows.

• If there is a tie, in order_by values, at the N-th row of the table, then all tied rows are returned. Then, when there are ties at the N-th row the function might return more than n rows.

You can read these articles below for more information

Refer:

Filtering the Top 3 products for each category in Power BI

Filtering the top products alongside the other products in Power BI

Showing the top 5 products and Other row

Best Regards,
Community Support Team _ Zeon Zheng

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