Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Rank issue

Hello,

I am trying to Rank Category Id from category table   as per Quantity sold in quantity sold table.
please refer the below table- 


I am using this formula- 

Rankbycateory = RANKX(ALLselected('Product'[CategoryID]),CALCULATE(SUM(QuanitySold[Qauntity Sold])))
But it's not working..Is it because of no direct relationship between the Category and quantity sold table?

Kindly advise further!

 

Need Help
  • Radhika2605's avatar
    Radhika2605
    5 years ago

    Hi, 

     

    Yeah I believe you should have a relationship between Category table as well as the Quanity sold table. Also, the formula looks a bit incorrect.

    Once the relationship is setup properly then try using this on.  I have separated the two measures so that it becomes more clear.

    Total Quanity = SUM(QuanitySold[Qauntity Sold]) 

    Rankbycategory - RANKX(ALLselected('Product'[CategoryID]), [Total Quantity], , DESC)

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Thanks

    Radhika

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try making join between product and Quantity sold as 1 to Many from product to Quantity sold

  • Radhika2605's avatar
    Radhika2605
    Helper II
    5 years ago

    Hi, 

     

    Yeah I believe you should have a relationship between Category table as well as the Quanity sold table. Also, the formula looks a bit incorrect.

    Once the relationship is setup properly then try using this on.  I have separated the two measures so that it becomes more clear.

    Total Quanity = SUM(QuanitySold[Qauntity Sold]) 

    Rankbycategory - RANKX(ALLselected('Product'[CategoryID]), [Total Quantity], , DESC)

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Thanks

    Radhika

     

     

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi  Anonymous ,

     

    Pls check whether there is some conflict relationships between "category" and "quantity sold"?

    As tested here,if you only have 3 tables with the relationships shown in original post,it works fine:

    If you still couldnt find the problem,would you pls share your .pbix file for test?

    Remember to remove the confidential information.

    For my sample .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!