Dax column - Available Quantity Calculation not working

[EricPad]

Hello - 

I have a relatively simple power bi report that works with Inventory data. 

The columns in my model are

• Item Code
• Item Code Desc
• Order Type
• Total Quantity on hand (Constant value per item code)
• Quantity Ordered
• Quantity BackOrdered
• Quantity Shipped
• Warehouse Code

I have created a Net On Order field with the folliowing data and it works correctly.

NetOnOrder =

    SWITCH (

        TRUE,

        vw_OpenOrdersByItem[OrderType] = "S", vw_OpenOrdersByItem[QuantityOrdered] - vw_OpenOrdersByItem[QuantityShipped],

        vw_OpenOrdersByItem[OrderType] = "B", vw_OpenOrdersByItem[QuantityBackordered],

        vw_OpenOrdersByItem[OrderType] = "M", vw_OpenOrdersByItem[MasterQtyBalance]

        )

I am trying to create an "Available" field which should be the Constant value of TotalQuantityOnHand minus my "NetOnOrder" column. 

I tried creating that like this:

Available =

    vw_OpenOrdersByItem[TotalQuantityOnHand] - vw_OpenOrdersByItem[NetOnOrder]

But this isn't working. The Available seems to be aggregating which I do not want. 

Here is a sample of the data

In this table I would expect the available value to be -10623 but instead it's coming up with 96249.

Any ideas what I could be doing wrong here?

[v-kaiyue-msft · Accepted solution]

Hi @EricPad ,

Thanks for the reply from Ashish_Mathur , please allow me to provide another insight:

You can create a measure.

Available = 
    MAX(vw_OpenOrdersByItem[TotalQuantityOnHand]) - SUM(vw_OpenOrdersByItem[NetOnOrder])

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[v-kaiyue-msft · Accepted solution]

Hi @EricPad ,

Thanks for the reply from Ashish_Mathur , please allow me to provide another insight:

You can create a measure.

Available = 
    MAX(vw_OpenOrdersByItem[TotalQuantityOnHand]) - SUM(vw_OpenOrdersByItem[NetOnOrder])

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[EricPad · Accepted solution]

This worked perfectly! Thank you so much! 

I tried something similar as a column but not a measure. Thank you again for your help!

[EricPad]

Hello - 

Here is the link:

https://drive.google.com/file/d/19J3TS6Vw7XeVKnt_-67gPraSd1BSAIec/view?usp=drive_link

Thank you!

Eric

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[v-kaiyue-msft]

Hi @EricPad ,

Ashish_Mathur , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution: 

Try setting the field to "don't summarize".

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[EricPad]

When I click to not summarize the available field for some reason the Net On Order field value changes to an incorrect value as well. 

I think the Available field needs to be some type of sumx value where for a given item code the sum of the Net On Order is subtracted from the static (per item code) Total Quantity on hand but I haven't been able to get the syntax correct. 

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Clearly show the problem there.

[EricPad]

where can I share it to?

[Ashish_Mathur]

Upload it to Google Drive and share the download link.