USERELATIONSHIP issue

[capitan_maximum]

Hi,

I am quite new to power bi in generall and DAX specifically. By now I can solve simple issues myself, but I struggle with the following one I really hope you can help me with:

• Background: In my companie's ERP-System there are two kinds of orders
  • stock orders are used to produce articels before customers place their orders
  • customer orders are ordinary contracts under which customers place their orders
• Example:
  • Our sales department knows we will sell about 100 x product
  • A stock order is being opened in order to produce 100 x product
  • One month later "custumer a" places an order for 40 x product and "customer b" places an order for 60 x product
  • Corresponding customer orders are being opened in the system for amounts of 40 and 60
  • Goods are being delivered and the orders closed
• Problem description:
  • If I want to list customer orders with costs in another column I can only see the costs which have accumulated directly under the customer order (for example delivery or packaging)
  • This means that I won't see costs which have accured during production, because they are listed under the stock order.
  • In order to see production costs I first need to know the percentage of products from a stock order that have been assigned to a customer order
  • Afterwards I need to multiply the percentage with the total costs of the stock order and list them next to the customer order

• Goal: My report has to look something like this:
  • OrderPositionID
  • OrderType // I only want to see customer orders so I'll filter the column by customer orders
  • Revenue // Of the customer order
  • Costs // These are the costs which have accured under the customer order
  • Costs // This is the portion of the costs which have occured during production under the stock order
  • Gross Profit // Measure = Revenue - costs (customer order) - costs (stock order)

Data-Modell: There are two tabels.

Tabel 1: OrderPosition

• OrderPositionID
• OrderType (customer or stock)
• Revenue
• AmountTotal
• Costs

Tabel2: Name: Assigned

• OrderPositionCustomerID
• OrderPositionStockID
• AmountAssigned

Relationships

• Active (both directions): Assigned[OrderPositionCustomerID] * to 1 OrderPosition[OrderPositionID]
• Inactive (both directions): Assigned[OrderPositionStockID] * to 1 OrderPosition[OrderPositionID]

What I tried so far to get the percentage of assigned products:

Costs (stock order) :=
SUMX (
    RELATEDTABLE ( Assigned );
    Assigned [AmountAssigned]
)
    / CALCULATE (
        SUM ( OrderPosition[Amount] );
        USERELATIONSHIP ( OrderPosition[OrderPositionID]; Assigned[OrderPositionStockID] )
    )

I hope you can understand what I am trying to explain. This is quite hard for me to describe (especially in english). Thanks a lot!

[v-frfei-msft · Accepted solution]

Hi @capitan_maximum,

I made a sample as your description and create some measures.

costs customer1 = CALCULATE(SUM(OrderPosition[Costs]))

Costs multiply = [Costs (stock order)]*CALCULATE(SUM(OrderPosition[Costs]),FILTER(ALL(OrderPosition),OrderPosition[OrderType] = "stock"))

Gross Profit = SUM(OrderPosition[Revenue])-[Costs multiply]-[costs customer1]

Costs (stock order) = 
SUMX (
    RELATEDTABLE ( Assigned ),
    Assigned [AmountAssigned]
)
    / CALCULATE (
        SUM ( OrderPosition[Amount]),
        USERELATIONSHIP ( OrderPosition[OrderPositionID], Assigned[OrderPositionStockID] )
)

Then we can get the result as below.

For more details, please check the pbix as attached. If that doesn’t meet your requirement, kindly share your sample data and the excepted result to me.

https://www.dropbox.com/s/htmojepzj7nqs8j/USERELATIONSHIP%20issue2.pbix?dl=0

Regards,

Frank