Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone!
I need to calculate based on our order master the ration between order quantity and order quantity shipped.
Basically the order master has the following structure:
Order | Order Date | Order Qty | Order Qty Shipped |
1 | 01.01.2020 | 1000 | 1050 |
2 | 01.01.2020 | 2000 | |
3 | 01.01.2020 | 2000 | 1950 |
I need to calculate the ratio between these two columns just for order that are completed. That means there is a quantity in the last column.
This is my first approach:
Ratio=
VAR QTYShipped = SUM(OrderMaster[Quantity Shipped])
VAR QTYPlan = SUM(OrderMaster[Order Quantity])
RETURN
IF(HASONEVALUE(OrderMaster[Quantity Shipped]),BLANK(),DIVIDE(QTYPlan,QTYShipped))
thanks to both of you.
I am using a Item Master that has for each code a corresponding area.
On item level both approaches are working perfectly!
On area level there is no result with the first approach.
Why is that so?
Because at Area level there is no HASONEVALUE there are MULTIPLEVALUES, try USING ISINSCOPE
Ratio =
VAR QTYShipped =
SUM ( OrderMaster[Quantity Shipped] )
VAR QTYPlan =
SUM ( OrderMaster[Order Quantity] )
RETURN
IF (
ISINSCOPE ( OrderMaster[Area] ),
"Then do something here",
IF (
ISINSCOPE ( OrderMaster[Quantity Shipped] ),
DIVIDE ( QTYPlan, QTYShipped ),
BLANK ()
)
)
Thanks a lot!
Why do I get at area level a false value?
It divides the total QTYShipped with total QTYPlan but it should be the average based on item level.
How can I adjust this?
Here you are:
Area | Order | Quantity Shipped | Order Quantity | Ratio |
A | 40097113 | 2028 | 2222 | 91,27% |
A | 40097114 | 2294 | 2439 | 94,05% |
A | 40097115 | 2795 | 2439 | 114,60% |
A | 40097116 | 2250 | 2439 | 92,25% |
A | 40097117 | 2586 | 2608 | 99,16% |
Power BI result | 98,40% | |||
AVG | 98,27% |
QuantityRatio :=
AVERAGEX (
SUMMARIZE ( OrderMaster, OrderMaster[Area], OrderMaster[Order] ),
DIVIDE ( [QTYShipped], [QTYPlan] )
)
Hi @joshua1990 ,
Are you looking for the average as 98.27% to show up in the total ?
You can create 3 measures
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
Hi @joshua1990 ,
Try this measure
Ratio =
VAR QTYShipped = SUM('Table'[Order Qty Shipped])
VAR QTYPlan = SUM('Table'[Order Qty])
RETURN
IF(MAX('Table'[Order Qty Shipped]) = BLANK(),BLANK(),DIVIDE(QTYPlan,QTYShipped))
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
That's because you are returning BLANK when there is a single value of Quantity Shipped in the filter context and the only time anything is returned is at subtotal or grandtotal because at that level there are multiple Items. Your measure should be like this.
Ratio =
VAR QTYShipped =
SUM ( OrderMaster[Quantity Shipped] )
VAR QTYPlan =
SUM ( OrderMaster[Order Quantity] )
RETURN
IF (
HASONEVALUE ( OrderMaster[Quantity Shipped] ),
DIVIDE ( QTYPlan, QTYShipped ),
BLANK ()
)
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |