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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.