Sum sales quantity of gift item when sales order includes a gift tag

[abujouz86]

Hello,

I can't figure out the solution to this challenge and how to write the dax formula. My dataset is a sales table where each row is a line of the sales order. The table includes a column for the sales order number, column for the item number, and column for the unit sales quantity.

We have gift items that can be sold with a gift tag, and each has an item number. When sold together they have the same sales order number - the gift item (e.g. 000954) and the gift tag (e.g. 000307) but are separate lines on the sales order. The gift tag can also be included on the sale of a different gift item (e.g. 000957). These two gift items (000954 and 000957) can also be sold with a different gift tag (e.g. 000308).

I would like to separately sum the quantity of sales of the gift items when the sale includes one of the gift tags, and exclude the sales quantity of the gift items when the sale does not include the gift tag.

Below is a sample of the data, thank you for your advice and help!

Order number	Item	Sales quantity
63970456	000307	1
63970456	000957	1
63970560	000957	1
63970602	000957	1
63970848	000957	1
63970891	000957	1
63971810	000954	4
63971923	000954	1
63971931	000307	1
63972498	000308	1
63972561	000954	1
63972896	000308	1
63972896	000954	1

[abujouz86 · Accepted solution]

I created 2 new tables - one filtering the item number column excluding the gift tag items and the other table including only the gift tag items. Then I joined the tables on the sales order number and customer address. 

[amitchandak]

@abujouz86 , Not very clear. Try new measures like

Gift item Sum =sumx(filter(Sales, Sales[Item] in {"000954","000307"}), Sales[Sales quantity])

Non Gift item Sum = sumx(filter(Sales, Sales[Item] not in {"000954","000307"}), Sales[Sales quantity])

[abujouz86]

Thank you. But this adds the gift item and the gift tag, I only want the sales quantity for the gift item if it includes the gift tag on the sales line. 

In other words, if the sales order number includes both the gift item and the gift tag sum only the sales quantity for the gift item.

[CheenuSing]

Hi @abujouz86 ,

Can you share the data in an excel file and load it to Google Drive or One Drive and give the link to access here.

Also for the sample data what is the output expected.

Cheers

CheenuSing

[abujouz86]

Thank you. The expected output should be the running total of the gift item (e.g. 000954 or 000957) if the sales order includes the gift tag (e.g. 000307 or 000308). I don't want to add the sales quantity of both the gift item and the gift tag.

In other words, if the sales order number includes both the gift item and the gift tag sum only the sales quantity for the gift item.

One drive link