Forum Discussion
Dax column - Available Quantity Calculation not working
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.
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?
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.
This worked perfectly! Thank you so much!
I tried something similar as a column but not a measure. Thank you again for your help!
9 Replies
Hi,
Share the download link of the PBI file. Clearly show the problem there.
where can I share it to?
Upload it to Google Drive and share the download link.
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.
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.
Hello -
Here is the link:
https://drive.google.com/file/d/19J3TS6Vw7XeVKnt_-67gPraSd1BSAIec/view?usp=drive_link
Thank you!
Eric
Hi,
PBI file attached.
Hope this helps.
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.
This worked perfectly! Thank you so much!
I tried something similar as a column but not a measure. Thank you again for your help!
