Forum Discussion
linked purchased order table not showing correct data
Hi there,
I have this requirement to show in one report an Inventory status showing the ff:
-products sold per month for a given year and per location.
- products ordered from suppliers from the same year.
I already have the required tables as shown on the model.
On my report, I have a slicer that filters by year, a text search slicer that filters
based on user input (barcode, internal reference no. or by product name)
Required output:
When I clicked a year from slicer, it should display products sold in a table visual,
a matrix showing products sold per month, another matrix to show per location and
a table to show quantity of products ordered from suppliers. This goes the same when I enter a barcode, internal reference no.
or name of a product on the text search slicer.
Issue:
Breakdown of sold quantities per month and per location are showing fine but the
ordered quantity for that product is giving a wrong quantity. What seems to be the issue on this?
Does this part require to create a new measure? I'm not familiar with DAX yet. Any input is highly appreciated.
Thanks in advance.
14 Replies
- audreygerred
Super User
Hi! Right now it looks like the visual that you have on your canvas for selecting a year is a table visual instead of a slicer. For the order quantity it looks like it is displaying a count, but I am assuming you want a sum. Check to make sure the field that has your quantity ordered is set to whole number and not text. Then, create a measure of Quantity Ordered = SUM ('YourTable'[YourFieldName])
Hi there,
I tried also the SUM but it's still giving me a wrong quantity. Sample product on my screenshot has value for Total Quantity ordered for year 2023 but none on 2024.
- Ashish_Mathur
Hi,
Share the download link of the PBI file and show the problem there very clearly.
Hi there! The issue I'm having right now is that when I linked products to Purchase orders using the internal reference no.,
I'm getting this message:
I need to link Products to Purchase Orders so I can extract the products purchased in a Purchase Order.
I think this will not follow the standard Star schema as I also need to link products in POS Orders table to extract the Products Sold. This will create ambiguity right? Any other input on this? Thanks in advance.
- saud968
Memorable Member
I believe the issue is due to the relationship between POS Sales and Purchase Order with the Date table as the Cross-Filteration is both set to Many-to-One it should fix it.
Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!Hi there,
I tried setting the Cross Filter direction to Single for both POS Sales and Purchase Order relationship to Date table. Now, it's giving me a blank result. In my data, product has purchase orders for year 2023 and none on 2024 so it should show quantity when I select 2023. I'm not sure if I'm getting your advise right. Am I missing something here?
In addition, if I change the cross filter direction from Single to Both in either POS Sales or Purchase Order table relationship to Date, I'm getting this error:
In addition, when I proceed with adding the relationship to either POS Orders to Product or Purchase Orders when a relationship is already linked to either one, I'm getting this message:
