Aggregate functions to use in measure

[gunasai]

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, 

Actual Qty_picked = sum('Picks_table'[QTY_Picked])/MIN(item_table[Case_qty])

Is there another measure to solve this issue?

Any help would be appreciated. Thanks so much

[v-easonf-msft]

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

[gunasai]

Thank you for your reply!

[Ashish_Mathur · Accepted 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])

[gunasai]

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 

[Ashish_Mathur]

You are welcome.

[lbendlin]

@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])))

[gunasai]

Thank you for your response!