Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys,
I have two tables connected with a 1-many relationship
Item_table
item_code | case_qty |
1028445 | 6 |
Picks_table
Item_code | Qty_picked | item_size | Picker_id |
1028445 | 42 | 90ct 990mg | abc |
1028445 | 24 | 90ct 990mg | abc |
1028445 | 750 | 90ct 990mg | abc |
1028445 | 30 | 90ct 990mg | abc |
The actual qty picked is 141 because the picked is case of 6 eaches. Hence, 846/6 = 141 cases
Why does it not aggregate to the measure value i.e., 141.
It is currently showing a total value.
The measure I wrote for Actual qty_picked is as follows,
Solved! Go to Solution.
Hi,
Ensure that you have a seperate table called Item_Code will all unique items listed in that table. There should be a replationship (Many to One and Single) from the Item_code column of both tables to the Item code column of the 2 tables that you have. To your visual, drag Item code from the new table. Write these measures:
Case qty = sum(item_table[case_qty])
Picked qty = sum(Picks_table[qty_picked])
Measure = divide([Picked qty],[Case qty])
Measure1 = averagex(values(Item_code[Item_code],[Measure])
Hi, @gunasai
Can you show your more records in item_table?
I think you need to check whether the return value of MIN(item_table[Case_qty]) is 6 rather than 1.
Best Regards,
Community Support Team _ Eason
Thank you for your reply!
Hi,
Ensure that you have a seperate table called Item_Code will all unique items listed in that table. There should be a replationship (Many to One and Single) from the Item_code column of both tables to the Item code column of the 2 tables that you have. To your visual, drag Item code from the new table. Write these measures:
Case qty = sum(item_table[case_qty])
Picked qty = sum(Picks_table[qty_picked])
Measure = divide([Picked qty],[Case qty])
Measure1 = averagex(values(Item_code[Item_code],[Measure])
The Item_table that I mentioned above is a table with all unique items listed 🙂
With your help,
I could figure out my
Measure 1 = averagex(item_table, [Measure]) (For Average)
Measure 1 = sumx(item_table, [Measure]) (For Sum)
This is what I wanted. Thanks for your help, @Ashish_Mathur
You are welcome.
@gunasai Your sample data may not be covering the issue fully. Here is a measure that works, but you need to check it against more data.
Actual Qty_picked = sumx(Picks_table,divide(Picks_table[Qty_picked],SELECTEDVALUE(item_table[case_qty])))
Thank you for your response!