ranking based on a measure

[Anonymous]

Hello community,

Hope you are well.

I would appreciate your help with the below ranking problem.

Description :

I extract the shipment report and I try to calculate the number of pallets to be despatched.

This is calculated as a measure, where I divide the order quantity by the pallet quantity columns.

My data model includes dimension tables such as

• product status, linked via product column
• supplier area, linked via supplier column
• market area, linked via market column
• product category, linked via product column
• dates, linked via shipment date column

Watchout :

My fact table combines all extractions, timestamped based on their extraction table.

Task :

Based on the number of pallets, I want to demonstrate the ranking per

• market
• supplier
• market area
• supplier area

This ranking will have to be based on the latest data extraction set.

Fact table

Extraction_Date	Product	Supplier	Market	Shipment_Date	Order_Quantity	Pallet_Qty
01/01/2021	Prod.1	Supplier.1	Market.1	02/01/2021	100	100
01/01/2021	Prod.1	Supplier.1	Market.1	03/03/2021	200	100
01/01/2021	Prod.2	Supplier.1	Market.2	15/02/2021	80	40
01/01/2021	Prod.3	Supplier.2	Market.2	16/03/2021	30	30
01/01/2021	Prod.4	Supplier.3	Market.3	16/02/2021	40	20
01/01/2021	Prod.4	Supplier.3	Market.3	25/04/2021	20	20
02/03/2021	Prod.1	Supplier.1	Market.1	03/03/2021	200	100
02/03/2021	Prod.2	Supplier.1	Market.2	10/04/2021	120	40
02/03/2021	Prod.3	Supplier.2	Market.2	16/03/2021	30	30
02/03/2021	Prod.4	Supplier.3	Market.3	25/05/2021	40	20
02/03/2021	Prod.5	Supplier.3	Market.3	12/03/2021	20	10
02/03/2021	Prod.5	Supplier.3	Market.3	14/04/2021	10	10

Dimension tables

Supplier	Supplier_Area
Supplier.1	SupplierArea.1
Supplier.2	SupplierArea.1
Supplier.3	SupplierArea.2

Market	Market_Area
Market.1	MarketArea.1
Market.2	MarketArea.2
Market.3	MarketArea.3

Product	Status
Prod.1	Status1
Prod.2	Status1
Prod.3	Status1
Prod.4	Status2
Prod.5	Status2

Product	Category
Prod.1	Category1
Prod.2	Category1
Prod.3	Category2
Prod.4	Category3
Prod.5	Category4

Thank you in advance,

George

[lbendlin]

" This is calculated as a measure, where I divide the order quantity by the pallet quantity columns. "

Why is this a measure?  Both values are in the same row, so a calculated column is sufficient.

What's the reason for having separate Product Status and Product Category tables?  These should be combined.

Ranking means assigning positions.  Do you want to rank based on who has more pallets per order or less?

Most of your requirements seems to be covered by a sort instead of a rank?

[Anonymous]

Hello @lbendlin 

Thank you for your reply, please see my inputs below.

I try to avoid having calculated columns if I can have a measure instead.

The product status is a separate table because the same product can have different statuses at different markets (for example it canm be active in market 1, but run-down in market 2). Thus, the table key is the combo product&market.

Thank you,

George

[lbendlin · Accepted solution]

"I try to avoid having calculated columns if I can have a measure instead."

That is a fallacy.  Remember that your ultimate goal is to provide a satisfying end user experience. Calculated columns are better than measures in that respect.

"the table key is the combo product&market"  - in that case you will have to add a composite key on both sides of the relationship. You can create that in DAX, in Power Query or in your data source (preferred).