CODEWITHSUNDEEP
powerbidimensional-modelingstar-schema
variable
variable

Reputation: 9714

How to handle order line level dimension when used along side a Order level measure?

I have a header-detail type of requirement.

For example:

My dimensions are product, customer, DateTable

My fact table is FactOrders (columns: order num, prod_id, cust_id, date, num of items, total amount, cost of pkg).

This is a star schema.

There was a new requirement to add/show data for order line items, and to make a drill through such that when a user clicks on an order they should be able to drill through to a new page showing the order lines.

So I merged the FactOrders with FactOrderLines.

Since cost of pkg is an order level figure (and repeats for each line due to the merge), following measure is created:

Cost of pkg = SUMX(VALUES('FactOrders'[Order Num]), CALCULATE(MIN('FactOrders'[Cost of pkg])))

In a table visual if I place the OrderNum and above measure I get correct result.

But if I add the Product Name (from Product dimension, which is only applicable to Order Lines) into the visual, I get an incorrect result. How to handle this?

Upvotes: 0

Views: 122

Answers (1)

NickW
NickW

Reputation: 9798

All your measures need to be at the same grain in a fact table. Where you have a grain of order items but measures at the order level you have a number of options (this is not a necessarily a definitive list of options):

  • Allocate the order measure to the line items
    • Accurately if there are business rules that can be defined to do this
    • Arbitrarily if necessary (equally to all lines, all to one line, etc)
  • Create a dummy line item to hold the order measure e.g. with a product code of “pkg”

Which option you choose depends on how you are going to use the data and how easy it is to include/exclude, as necessary, the order measures from your processes

Upvotes: 0

Related Questions