Rankx Return Wrong Results

[Anonymous]

Hi community,

I'm having some issues with a ranking formula. It does not return the expected results based on the imput and I cant figure out what goes wrong. I'm trying to rank product groups (franchises) on sales for a filtered brand. As you can see it returns the wrong ranks - two items ranked 7th while their sales are different. (ranks vary based on the date and brand filtesr but are almost never correct)

I'm using the following ranking formula (which I've used plenty of times and always returned the expected result until now)

Rank =

rankx(
ALLSELECTED(
'D - Product'[Item Aka Desc]
);
CALCULATE(
[Net Sales Value]
)
)

I'm using a dataset with a sales table containing date, item barcode, and sales value. The sales table is connected to a date table and a product table containing barcode, brand, and franchise. The visual is filtered on brand and date. I've excuded two franchises from the visual, added a visual filter to return only the top 10, and dont want to inlcude franchises with no sales value (removing these visual filters does not affect results).

Has anybody ever experienced something similar with a ranking measure and can help with solving this issue?

Thank you in advance!

[v-xicai]

Hi   @Anonymous ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[Anonymous]

Hi @v-xicai,

We already use a star schema. I changed the filter directions to both and added the changes to the measure you recommended. Unfortunately the error persists.

Do you know if it is possible to recalculate the value Rankx uses to rank the franchises? Since some franchises get the same rank while they have different sales I assume Rankx somehow assigns them the same sales value.

If you have any other suggestions that would be realy welcome.

[v-xicai]

Hi @Anonymous ,

You may check your relationship first of all, recommend you create the star schema instead of circle schema,  and change the Cross filter direction of relationships among the these tables above from Single to Both , which will take these tables treated as a single table. See more:Create and manage relationships in Power BI Desktop . Then it will return accurate corresponding result when you interact or filter someone field in related tables.

Then you may create measure like DAX below.

Rank =RANKX(ALLSELECTED('D - Product'[Item Aka Desc]), CALCULATE(SUM([Sales])),, Desc, Skip)

Best Regards,

Amy 

Community Support Team _ Amy

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

[amitchandak]

@Anonymous , All selected will not help much. refer

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[Anonymous]

Hi Amitchandak,

Thank you for your reply. I already tried the Radacad methods but they return the same issues as with my formula. The reason I use Allselected is that I use filters on the visual that I dont want to include in the ranking calcuation.

The TopN method returns the sales value (same as when I use a standard measure) but does not show a ranking number.