Forum Discussion

sqlguru448's avatar
sqlguru448
Helper III
5 years ago
Solved

Ranking measure with Table variable

Hello Folks,    I'm trying to create measure for Top 10 customers based on sales amount in Power BI which is connected Live to the Tabular model.   Customer is Dimension and Sales amount is in fa...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi sqlguru448 ,

     

    Be aware that making a summarizaition of a full table will bring for sure performance issues, in your case you are using the FACT table that I assume have a huge number of data so be aware of that.

     

    Try the following code:

     

    Measure = 
var temp_table =
        FILTER (
            SUMMARIZE (
                ALLSELECTED ( Customer[Name] );
                Customer[Name];
                "Total_Sales"; [Sales Amount]
            );
            [Sales Amount] > 0
        )

var Ranking = RANKX(temp_table; [Sales Amount])


RETURN
    Ranking

     

     

    Then filter out all rankings above 10.

  • sqlguru448's avatar
    sqlguru448
    Helper III
    5 years ago

    Hi vivran22 

     

    Below is the sample data screenshot. The ranking should be from 1 to 10 based on highest sales amount. but my above dax gives me Rank 1 in all rows.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      5 years ago

      Hi,

      Try this measure

      =rankx(all(data[Customer]),[Sales])

      I have assumed that sales is an explicit measure.

      Hope this helps.

      • sqlguru448's avatar
        sqlguru448
        Helper III
        5 years ago

        Ashish_Mathur 

        This has performance issue when other dimesions are added to the visual, that is why I am trying to get top 10 first within variable table and then add other dimensions to it. Thank you.