Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Rand() recalculated within TOPN

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?

 

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

1 Reply

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    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.