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.

MFelix's avatar
MFelix
Super User
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

Thank you this gave me an idea to modify my dax.

 

Measure = var _top10 =
FILTER(SUMMARIZE(ALLSELECTED('Fact Sales'), Product[Product_Name], Product_Subcategory[Product_subcategory_name,  location[state] , "Sales", [Total Sales]), [Total Sales] > 0 )
RETURN RANKX(_top10,[Total Sales])
 
Thank you for your help MFelix 
  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi sqlguru448 ,

     

    Glad I could point out in the correct direction, I made the measure with only a few lines of data so it could work incorrectly on a bigger scale.

     

    Always remenber that FILTERING a full table will have a very big impact on performance, you should always filter on the minimum amount of columns needed for your calculations and your context needs.