Unable to use RANKX () to calculate rank for both Category and Sub-Category

[SaurabhDas85]

Hi,

I have a datasets of "Company_Sales Invoice Line", "Company_Item" and "Company_Product Group". I have applied RANKX() to calculate rank for both Product Group (Category) and Item (Sub-Category). However, it is not giving me the desired result.

Request someone to highlight what am I doing wrong here. Measure 1 is just calculation of the Invoice Qty which is mentioned in the data set as KGs. So it has been converted to Metric Tonnes by dividing by 1000.

Mentioned below is the DAX code that I am using and attached herewith is the photo of my dashboard where Rank of Products are not showing of correctly whereas the same is shown correctly for the item level.

Snapshot of the dashboard

Measure 1

Invoice_Qty = CALCULATE(DIVIDE(SUM('Company_Sales Invoice Line'[NetInvQty]),1000),FILTER('Company_Sales Invoice Line','Company_Sales Invoice Line'[Posting Date]<today()))

Measure 2

PRODRANK = RANKX(all('Company_Product Group'[Code]),[Invoice_Qty],,DESC)

Measure 3

DynamicRank =

SWITCH(TRUE(),

    HASONEFILTER('Company_Item'[No_]),

    RANKX(all('Company_Item'[No_]),[Invoice_Qty],,DESC),    

    ISINSCOPE('Company_Product Group'[Code]),

    [PRODRANK]

)

[SaurabhDas85]

https://drive.google.com/file/d/12_rtq3oyEnA9MtNLIr-LTZBFpdg0xdGl/view?usp=share_link

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[SaurabhDas85]

https://drive.google.com/file/d/12_rtq3oyEnA9MtNLIr-LTZBFpdg0xdGl/view?usp=share_link

[Ashish_Mathur]

Access Denied message

[SaurabhDas85]

Please confirm the email id which I have to give access to 

[Ashish_Mathur]

Make the file accessible to everyone.  I would not like to share my e-mail address.

[SaurabhDas85]

https://drive.google.com/file/d/16cBF6Y-UA5NucY7eNwEe5ONHmIJhDAnn/view?usp=sharing

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution file from here.

Hope this helps.

[SaurabhDas85]

Thanks a lot for the solution.

However, I also want to know what was wrong that I was doing. I was using the following code and the hierarchy was also correct i.e first the sub-category and then the category. Then was it not giving the desired result.

Measure 2

PRODRANK = RANKX(all('Company_Product Group'[Code]),[Invoice_Qty],,DESC)

Measure 3

DynamicRank =

SWITCH(TRUE(),

    HASONEFILTER('Company_Item'[No_]),

    RANKX(all('Company_Item'[No_]),[Invoice_Qty],,DESC),    

    ISINSCOPE('Company_Product Group'[Code]),

    [PRODRANK]

)

[SaurabhDas85]

It's done. No need to reply anymore

[Ashish_Mathur]

You are welcome.  It was with the filter condition of amount > 0.

[SaurabhDas85]

Try this one please

[amitchandak]

@SaurabhDas85 ,Based on what I got

The order should be reverse of the hierarchy, use isinscope at both places

IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM

[SaurabhDas85]

Please share your email id as well

[SaurabhDas85]

https://drive.google.com/file/d/12_rtq3oyEnA9MtNLIr-LTZBFpdg0xdGl/view?usp=share_link

[SaurabhDas85]

Can you please do the changes in the file and share it with me. Something is there that I am not doing correctly