Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

DAX Rank with Grouping

I used the following measure to Rank items within their respective Product Families:

 

Product Family Rank - POS Sales = RANKX(ALLEXCEPT(Material,Material[Prod Hier: Product Family]),'POS Sales'[POS Sales])

 

It seemed to be working but there are a few instances where it is wrong. See below. The highlighted item should be listed as 1 because its the top item by POS Sales in the Soft Coolers Product Family, yet its showing 2.

 

Any ideas how to resovle?

 

Data Protection
Need Help
Show and Tell
Tips and Tricks

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Anonymous , Try measures like

     


    Create a measure assuming [POS Sales] is a measure

    M1= calculate([POS Sales], filter(allselected(Material), Material[Prod Hier: Product Family] = max(Material[Prod Hier: Product Family])))


    Product Family Rank - POS Sales = RANKX(allselected(Material[Prod Hier: Product Family]),[M1])

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      No matter how much I filter down, I am still seeing "this visual has exceeded the available resources" when I input this formula in. Do you have an alternative?