Forum Discussion

KRISH80's avatar
KRISH80
Icon for Helper II rankHelper II
6 years ago

Calculate TOP customer with multiple criteria

Dear All,

I need to RANK my Top customers my GP%. Below table has Customer Name, Contract Value, Profit and GP%.

But one condition that I need to apply is TOTAL Profit from the customer should be greater than 50K. From the below example, Accenture should not show up in my list as the TOTAL profit is less than 50K.

 

My current DAX to RANK is 

RANKX(ALL(FUNNEL[Customer Name]),[TOTAL-GP%], , DESC)
 
HOw should i modify this DAX to add that criteria of >50K ?
 

 

2 Replies

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    6 years ago

    Hi KRISH80 ,

     

    Try modifying your DAX as follows:

    RANKX(FILTER(FUNNEL, [TOTAL] > 50000), [TOTAL-GP%], , DESC)

     

    If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

     

    Thanks,

    Pragati

    • KRISH80's avatar
      KRISH80
      Icon for Helper II rankHelper II
      6 years ago

      Hi, This is not heping. Below is the actual DAX i am using. Where i have dynaic filed like Ranking Dimension and Ranking Select. In this code i need to add that condition of selecting ONLY accounts which have TOTAL-Gp of >50K

       

      Top Accounts by Rank (GP%-OPEN) =
      VAR
      RankingDimension =VALUES(FUNNEL[Account Name])
      VAR
      RankingSelect = [Ranking Select]
      RETURN
      CALCULATE([TOTAL-OPEN-GP%],
      Filter( RankingDimension,RANKX(ALL(FUNNEL[Account Name]),[TOTAL-OPEN-GP%], , DESC) <= RankingSelect))