cancel
Showing results for
Did you mean:
Post Prodigy

## Calculate Ratio between Order Quantity and Order Shipped

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))``````

Unfortunately, this is not working since I get no result in my matrix on order level.
I just get a result for the grand total.

Has someone any idea?

9 REPLIES 9
Post Prodigy

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?

Resident Rockstar

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 ()
)
)``````
Post Prodigy

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?

Resident Rockstar
Can you please show the result in a matrix, with Area above quantity shipped, we might have to tweak the values with CALCULATE and ALL/ALLSELECTED construct
Post Prodigy

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%
Resident Rockstar
``````QuantityRatio :=
AVERAGEX (
SUMMARIZE ( OrderMaster, OrderMaster[Area], OrderMaster[Order] ),
DIVIDE ( [QTYShipped], [QTYPlan] )
)``````
Super User

Hi @joshua1990 ,

Are you looking for the average as 98.27% to show up in the total ?

You can create 3 measures

Quantity Ordered Measure = SUM( 'Table'[Order Quantity])

Quantity Shipped Measure = SUM ( 'Table'[Quantity Shipped])

Ratio Measure = AVERAGEX(
'Table',
DIVIDE([Quantity Shipped Measure], [Quantity Ordered Measure]))

I am assuming this is a different question asked in the same post.

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

Super User

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!

Resident Rockstar

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 ()
)``````

Announcements