cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joshua1990
Post Prodigy
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:

OrderOrder DateOrder QtyOrder Qty Shipped
101.01.202010001050
201.01.20202000 
301.01.202020001950

 

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
joshua1990
Post Prodigy
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?

 

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?

 

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

Here you are:

 

AreaOrderQuantity ShippedOrder QuantityRatio
A40097113  2028222291,27%
A40097114  2294243994,05%
A40097115  27952439114,60%
A40097116  2250243992,25%
A40097117  2586260899,16%
     
   Power BI result98,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

 

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]))
 
 
1.jpg
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!

 
harshnathani
Super User
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))

 

 

1.jpg

 

Regards,

Harsh Nathani


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


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

AntrikshSharma
Resident Rockstar
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 ()
    )

 

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors