RANKX All Items by Category Based on Another Table Value

[DarylM]

Hi, 

I am trying to get a rank of items and categories available in DimTable1, by all units shipped in FactTable1, but be able to select a single customer in FactTable2 and still provide the rank based on all items by category from FactTable1.

I am using the following right now, but I am not able to get it to order ASC or Skip Ties. 

Rank = RANKX(ALLEXCEPT('DimTable1','DimTable1'[Store Sub-Category]),[Tot. Units Shipped])  

#"Tot. Units Shipped" measure is the sum of FactTable1 Units Shipped. 

This is intended to be able to view the items a customer is not buying based on the ranking of all purchases. 

Any help would be appreciated. 

[v-ljerr-msft]

Hi @DarylM,

---

I am using the following right now, but I am not able to get it to order ASC or Skip Ties. 

Rank = RANKX(ALLEXCEPT('DimTable1','DimTable1'[Store Sub-Category]),[Tot. Units Shipped])  

#"Tot. Units Shipped" measure is the sum of FactTable1 Units Shipped. 

---

Could you try the formula below to see if it works in your scenario? 

Rank =
RANKX (
    ALLEXCEPT ( 'DimTable1', 'DimTable1'[Store Sub-Category] ),
    [Tot. Units Shipped],
    1,
    SKIP
)

Regards

[DarylM]

Hi, thanks for the feedback. Add those two values just makes every rank number turn to 55. 

I have a both direction relationship between the FactTable1 and DimTable1 based on the item number. 

I am not even sure I am using the best formula to do this ranking. 

Thanks again. 

[Anonymous]

@DarylM

Can you make an example of the desired result?

[DarylM]

Yes. Attached is what I am trying to get to. 

1. All Items from DimTable1

2. Customer Units Shipped From FactTable2 

3. Ranked By DimTable Category based on Units from FactTable1.

[Anonymous]

 @DarylM

Alright, and why do you have 2 fact tables?

[DarylM]

Because I need to rank all items in inventory by all sales will filtering by an individual customer without filtering out all items. 

I am using the item numbers from the DimTable in the result image, rank based on fact table1 and units from fact table2. 

Maybe I am going about this all wrong too. 🙂

Would it woudl better if I just created a summary table of fact table 1 and ranked all items then bring the rank in verses trying to rank the dim table based on a second one? 

[Anonymous]

@DarylM

So you want ranking within sub categories based on unit shipped to customers?

[DarylM]

Yes. Thanks!

[Anonymous]

 @DarylM

So I came up with the following. I'm not actually sure this solves your problem. This solves it to my understandning. It assumes you are only using the fact table.

Try it out.

Rank = RANKX(ALLEXCEPT('Fact table';'Fact table'[Customer];'Fact table'[Sub-category]);CALCULATE(SUM('Fact table'[Units shipped])))

Let me know how it goes.

Was able to yield this result:

[DarylM]

Ok thanks! I will give it a shot! Appreciate it!

[Anonymous]

@DarylM

Let me know how it goes, and don't forget to give kudos and accept a solution if it works out.

[DarylM]

Thanks for the suggestion, but this does not really solve my problem. Thee formula worked when just using the main fact table, but I still have th issue of duplicated rank valus in a sub-catgory where several items total units are the same. 

I tried using ASC and SKIP along with the formula offered, but that didn't work. 

[Anonymous]

Yea, I can see why that's a problem. I didn't encounter this case, since I random generated some sample data.

Will get back to you @DarylM later on today.

[Anonymous]

@DarylM

I've been thinking about this all day and searching the net for how to go about the duplicate of value issue, and I simply can't find a viable solution, when we are trying to achieve dynamic index.

If the index is static, it is no problem, but you are trying to achieve indexing within a sub-category, which then demands the index to be dynamical. Weird bugs like this is what happens when you are trying to make indexing (or ranking if you will) dynamical.

Maybe it is possible to impose some conditions on the formula to make it add or subtract to the rank duplicates, but I'm not aware of such solutions for now. I'm still looking into it, and hopefully i find a solution.

[DarylM]

@Anonymous

Thanks for your help! I understand. I saw something regarding COUNTROW used to rank, but not sure that will work in my scenario.

[nickchobotar]

@DarylM

Regarding posting a pbix file to this forum, you will have to upload it to dropbox or onedrive and then post the url here. It would be, though, FANTASTIC if we could upload files directly to this forum. 

Thanks

[DarylM]

Hi, 

Here is a link to Sample PBIX file. Though from @Anonymous comments above, sounds like there mabe not be a solution to what I am trying to do. 

https://dutchvalleyfoods-my.sharepoint.com/personal/dmusser_dutchvalleyfoods_com/_layouts/15/guestaccess.aspx?docid=026f6b1ed54c340bba0c11e3b6f01e97e&authkey=AZlE3AW7TESM844zyCHl8Q8&expiration=2017-10-27T04%3a00%3a00.000Z

Thanks.

[nickchobotar]

@DarylM

Please post the sample data, so we can work on the real model.

N-

[DarylM]

@nickchobotar

I have a sample PBIX file, but how do I attach it in this forum? 

Thanks!