Forum Discussion

anjugauriunni's avatar
anjugauriunni
Frequent Visitor
2 years ago

Rankx function is not returning correct values when used with dimension table

Hi,  I made a item rank dax

Item rank = RANKX(All(Brand[Brand]), [Total Sales])

Brand is one of the dimension table containing brand name and brand id. Fact table contains brand id. The above function returns wrong rank values. If I replace the Brand[Brand] with brand id from fact table and put the Brand Id in the table column it returns the correct value.

 

Item rank = RANKX (All(Fact table[ Brand ID]), [Total Sales])

 

Total sales is sum of sales

Total Sales = Sum(fact table[ Sales])

 

When I put this along with Brand Id in the columns of table it showing correct value. If I put Brand name from the dimension table it is showing incorrect values.

I need the brand names in the table not the brand id. Couldanyone help on this please?

Thank you

 

Need Help

5 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    pls try this

     Measure = RANKX (ALLSELECTED(Brand[Brand],Fact table[ Brand ID]), [Total Sales])

    • anjugauriunni's avatar
      anjugauriunni
      Frequent Visitor
      2 years ago

      Error showing "All arguments of the ALLSELECTED function must be from the same table"

      • Ahmedx's avatar
        Ahmedx
        Super User
        2 years ago

        why take them from different tables?
        take them from where they are into one table


        Measure = RANKX (ALLSELECTED(Fact table[Brand],Fact table[ Brand ID]), [Total Sales])

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    Hi, anjugauriunni 

    try below

    item rank =
var a = addcolumn(
          values(brand[brand]),
         "amt", [total sales]
        )
 return
rankx(
    a,[amt]
)