Forum Discussion

sqlguru448's avatar
sqlguru448
Icon for Helper III rankHelper 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.

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
5 years ago

HI  sqlguru448 

Try to use this formula to create a measure

Measure 2 = 
VAR  _RANK= RANKX (
        ALLSELECTED ( Customer[Name] ),
        CALCULATE ( SUM ( 'Fact'[Sales Amount] ) ))
RETURN
IF ( _RANK <= 10,  _RANK)

 

if not your case, please share some simple sample data and your expected output.

 

Regards,

Lin

MFelix's avatar
MFelix
Icon for Super User rankSuper User
5 years ago

sqlguru448 ,

 

Just came out today check this videos about rankx and countrows.