Ranking a column in one table against related occurrences  [Solved]

Anything related to PowerPivot and DAX Formuale
Nerdio
Posts: 7
Joined: Thu May 13, 2021 6:40 pm

Ranking a column in one table against related occurrences

Postby Nerdio » Wed Sep 15, 2021 6:51 pm

I have two tables, one which is a list of SKUs, the other is a list of orders for those SKUs. Something like this.

SKU | Description
S001 | Item #1
S002 | Item #2
S003 | Item #3

Order No. | SKU | Quantity
O0001 | S001 | 2
O0002 | S001 | 3
O0002 | S003 | 1
O0003 | S002 | 5
O0004 | S001 | 2
O0004 | S003 | 3

I would like to create a Measure, so I can Rank the 'popularity' of each SKU. A result of something like this, based on the above.

SKU | Rank
S001 | 1
S003 | 2
S002 | 3

As a start I have tried this to count the number of orders for each SKU. (Just as a stepping stone to my final requirement)

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

NumOrders:=
VAR thisSKU = ItemTable[SKU]
RETURN
CALCULATE(DISTINCTCOUNT(OrderTable[Order No]), FILTER(OrderTable, OrderTable[SKU]=thisSKU))


This throws up an error because there is no aggregation on 'thisSKU'. My approach is clearly wrong, but being a bit of a DAX novice I am lacking some knowledge here. If someone could help me with the calculation of the number of orders for each SKU I should then be able to rank them.

TIA

Nerdio
Posts: 7
Joined: Thu May 13, 2021 6:40 pm

Re: Ranking a column in one table against related occurrences  [Solved]

Postby Nerdio » Fri Sep 17, 2021 5:14 pm

I have a solution.

NumOrders:=DISTINCTCOUNT(Orders[Order Num])

then

ItemRank:=IF([NumOrders]>0,RANKX(ALLSELECTED(Items[SKU]),[NumOrders]),BLANK())
Last edited by Nerdio on Tue Sep 21, 2021 4:58 pm, edited 1 time in total.

Trinder
Posts: 1
Joined: Tue Jul 20, 2021 9:28 pm

Re: Ranking a column in one table against related occurrences

Postby Trinder » Tue Sep 21, 2021 5:17 am

I often use Windowed Functions in the ORDER BY clause. It's super useful when I'm looking for examples in my data of frequently occurring / high number of events. For example...

SELECT *
FROM dbo.Episodes
ORDER BY COUNT(*) OVER (PARTITION BY PatientID) desc
Which will return all the rows in table Episodes, ordered by the patient with the most Episodes recorded.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 3 guests

cron