Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone!
I have to calculate the average delivery time multiplied by the Number of open Orders.
I have the following two tables:
tblItemMaster
Item | Area | Delivery Time | Value |
1 | A | 10 | 50 |
2 | B | 15 | 500 |
tblOrder
Order | Item | Status |
AA1 | 1 | 50 |
For this purpose I have two measures.
The first one to determine the number of Orders open (Status < 70).
# Order= CALCULATE(
COUNTROWS( tblOrder),
ALL( 'Calendar' ),
tblOrder[Status] < 70
)
And then I have an Avere function to determine the average delivery time:
Delivery Time = AVERAGE('tblItemMaster'[Delivery Time])
With a simple measure like this:
Test = Delivery Time*[# Orders]
> I get a false result.
I need to calculate this on the Area-level, not on Article Level.
For this I choose average, right?
What did I forget?
Solved! Go to Solution.
Not sure if this is what you wanted... but you can take it and shape it into something you want.
// Let's assume that
// 1. You've got an Orders table with
// orders in there OrderID, ItemID, OrderStatus
// where OrderStatus is the same for all lines
// with the same OrderID.
// 2. You've got an OrderItems table with
// items that belong to an order from the
// Orders table (ItemID is unique across all Orders)
// The columns in OrderItems are
// ItemID, Area, Delivery, TimeValue
// An open order is one that has OrderStatus < 70 in
// Orders.
// Relationship: Orders[ItemID] * <-one-way- 1 OrderItems[ItemID].
// One order can have multiple items but each item in
// OrderItems can belong to only one order.
//
// The above setup is a bit strange. What it really should be
// is this.
// Orders - stores data on orders and each line is one order.
// Items - stores all available items that an order can contain.
// Order2Item - stores the relationship between an order and an item.
[# Open Orders] =
CALCULATE(
DISTINCTCOUNT( Order[OrderID] ),
KEEPFILTERS( Order[Status] < 70 )
)
[Avg Delivery Time Per Order] =
IF( HASONEVALUE( Orders[OrderID] ),
AVERAGE( OrderItems[Delivery Time] )
)
[Avg Delivery Time Per Open Order] =
IF( [# Open Orders] = 1,
AVERAGE( OrderItems[Delivery Time] )
)
[Avg Delivery Time] =
AVERAGEX(
VALUES( Order[OrderID] ),
[Avg Delivery Time Per Order]
)
[Avg Delivery Time for Open Orders] =
AVERAGEX(
VALUES( Order[OrderID] ),
[Avg Delivery Time Per Open Order]
)
[Final Measure] =
[Avg Delivery Time for Open Orders]
* [# Open Orders]
Not sure if this is what you wanted... but you can take it and shape it into something you want.
// Let's assume that
// 1. You've got an Orders table with
// orders in there OrderID, ItemID, OrderStatus
// where OrderStatus is the same for all lines
// with the same OrderID.
// 2. You've got an OrderItems table with
// items that belong to an order from the
// Orders table (ItemID is unique across all Orders)
// The columns in OrderItems are
// ItemID, Area, Delivery, TimeValue
// An open order is one that has OrderStatus < 70 in
// Orders.
// Relationship: Orders[ItemID] * <-one-way- 1 OrderItems[ItemID].
// One order can have multiple items but each item in
// OrderItems can belong to only one order.
//
// The above setup is a bit strange. What it really should be
// is this.
// Orders - stores data on orders and each line is one order.
// Items - stores all available items that an order can contain.
// Order2Item - stores the relationship between an order and an item.
[# Open Orders] =
CALCULATE(
DISTINCTCOUNT( Order[OrderID] ),
KEEPFILTERS( Order[Status] < 70 )
)
[Avg Delivery Time Per Order] =
IF( HASONEVALUE( Orders[OrderID] ),
AVERAGE( OrderItems[Delivery Time] )
)
[Avg Delivery Time Per Open Order] =
IF( [# Open Orders] = 1,
AVERAGE( OrderItems[Delivery Time] )
)
[Avg Delivery Time] =
AVERAGEX(
VALUES( Order[OrderID] ),
[Avg Delivery Time Per Order]
)
[Avg Delivery Time for Open Orders] =
AVERAGEX(
VALUES( Order[OrderID] ),
[Avg Delivery Time Per Open Order]
)
[Final Measure] =
[Avg Delivery Time for Open Orders]
* [# Open Orders]
@joshua1990 , not sure I got it. You are filtering order #. no filter in avg time. So the number will not match with sum
@joshua1990 - Not sure I understand. I assume you have a relationship between your 2 tables, correct? On Item? Also, you are placing Area in your visual, correct? Any chance you can share an example PBIX?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |