Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX: Ranking a measure based on a Column

Hello All,   I am trying to create ranking of sales by customers and customer groups. Now these customers are been grouped as tier 1 and tier2.   But in visualization I am using only Customer co...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Anonymous ;

    Try it.

     

    Total Sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),[Customer Group]=MAX('Table'[Customer Group])&&[Customer]=MAX('Table'[Customer])))
    Top 5 Customers = 
    RANKX(
        FILTER( ALL('Table'),[Tier1/2]=MAX('Table'[Tier1/2])),
        CALCULATE(
            [Total Sales])
            
            ,,
            DESC,Dense)

     

    The final show:

    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Anonymous , You are using date in visual, and rank will distribute inside date.

 

In your, measure remove the date or use that in rank too

 

Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA

 

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      4 years ago

      Anonymous  Try if the two measures can help

       

      M1= CALCULATE(SUM('Table'[Sales]),,filter(allseleced('Table'),'Table'[Customer] = max('Table'[Customer]) && 'Table'[Customer Group] =max('Table'[Customer Group] )))


      Top 5 Customers =
      RANKX(
      ALL('Table'[Customer],'Table'[Customer Group], 'Table'[Date]),
      [M1]
      ,,
      DESC
      )

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thanks for the help amitchandak 

        Please check the question as i have updated the expected result.

        But it is not working as expected.

        Its returning the ranks for total sales but not as per the Customer groups.

         

        If you see the dax which i tried, giving the ranking for the Tier2 groups properly, but not to the Tier1 groups.

        Total Sales = CALCULATE(SUM('Table'[Sales]),ALLSELECTED('Table'[Begin of Week]))


Top 5 Customers  = 
    RANKX(
        ALL('Table'[Customer]),
        CALCULATE(
            'Table'[Total Sales],
            ALLEXCEPT('Table','Table'[Customer],'Table'[Customer Group])
            )
            ,,
            DESC
    )

         

         

        Two issues with the above query

        1 - It gives the same ranks for tier1 customers - should give the ranks for these customers as per the total sales values in desc.

         

        2 - When i remove the Customergroup column from the visual. ranks are given as per the total sales, but it should break as per the customer group.

        Need your help amitchandak 

         

        Thanks,

        Mohan V.