Rank issue

[Anonymous]

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!

[v-kelly-msft]

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!

[Radhika2605 · Accepted solution]

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

[amitchandak]

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