Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

ranking based on a measure

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_DateProductSupplierMarketShipment_DateOrder_QuantityPallet_Qty
01/01/2021Prod.1Supplier.1Market.102/01/2021100100
01/01/2021Prod.1Supplier.1Market.103/03/2021200100
01/01/2021Prod.2Supplier.1Market.215/02/20218040
01/01/2021Prod.3Supplier.2Market.216/03/20213030
01/01/2021Prod.4Supplier.3Market.316/02/20214020
01/01/2021Prod.4Supplier.3Market.325/04/20212020
02/03/2021Prod.1Supplier.1Market.103/03/2021200100
02/03/2021Prod.2Supplier.1Market.210/04/202112040
02/03/2021Prod.3Supplier.2Market.216/03/20213030
02/03/2021Prod.4Supplier.3Market.325/05/20214020
02/03/2021Prod.5Supplier.3Market.312/03/20212010
02/03/2021Prod.5Supplier.3Market.314/04/20211010

 

Dimension tables

 

SupplierSupplier_Area
Supplier.1SupplierArea.1
Supplier.2SupplierArea.1
Supplier.3SupplierArea.2

 

MarketMarket_Area
Market.1MarketArea.1
Market.2MarketArea.2
Market.3MarketArea.3

 

ProductStatus
Prod.1Status1
Prod.2Status1
Prod.3Status1
Prod.4Status2
Prod.5Status2

 

ProductCategory
Prod.1Category1
Prod.2Category1
Prod.3Category2
Prod.4Category3
Prod.5Category4

 

Thank you in advance,

 

George

1 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).

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

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.

lbendlin_0-1636670734456.png

 

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?

 

lbendlin_1-1636670890356.png

 

Anonymous
Not applicable

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

 

 

"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).

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.