Forum Discussion

danm's avatar
danm
Icon for Helper I rankHelper I
5 years ago
Solved

Top 10 - DAX formula optimization

Hi Experts,    I try to create a measure to create Top 1o Customer by number of products bought. I use the following formulas:   Customer Rank = RANKX(ALL(Sales_Fact[CustomerNumber]), [Total Sal...
  • AlB's avatar
    AlB
    5 years ago

    Hi danm 

    I'd need more details on the model, visual where measures are used etc. Can you share the pbix?

    The name for the measure [Top 10 Customers by Prod #] is a bit confusing, given what it seems to do.

    Try this in the meantime:

    Top 10 Customers by Prod # V2 =
VAR rank_ =
    RANKX ( ALL ( Account_Dim[Name] ), [No of unique Products] )
RETURN
    IF ( rank_ <= 10, [No of unique Products] )

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

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

danm , do you have any bi-directional joins. and having a slicer for those bi-directional joins. Make bi-directional join and try .

Small variation in second formula

 

No of unique Products = DISTINCTCOUNT(Sales_Fact[ProductNumber])

Top 10 Customers by Prod # =
CALCULATE( [No of unique Products],
TOPN( 10, ALL( Account_Dim[Name] ), [No of unique Products] ),
VALUES( Account_Dim[Name] ) )