Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors