Set analysis question

[#1]

Hi Everyone,

Thank you for taking a look at my question.

Here's the scenario:  I have a list of transactions and each transaction is made up of multiple line items that each belong to one department and have a net sales associated.  The data source is below:

TransactionKey	Department	NetSales
1	A	54
1	B	90
1	E	4
2	B	35
2	C	98
3	D	32
3	B	76
3	F	59
4	A	39
4	C	4
4	F	26

What I'm trying to do is create a straight table with Department as the dimension and have an expression that sums the entire transaction net sales for each transactions that has an item sold from the department.  The results would look like this:

Department	Net Sales
A	217
B	448
C	202
D	167
E	148
F	236

For example, department A has net sales of 217 because it TransactionKey 1 and 4 have an items sold from department A so I summed both of those transactions.

I've made several attempts to get the expression working by trying to aggregate Net Sales by TransactionKey but I'm not able to get it working properly.  If anyone could provide an example of the correct syntax I would greatly appreciate it!

Thank you,

Dan

[rustyfishbones]

Hi,

I am not sure I understand the question, SUM(NetSales) with Department as Dimension should give you what you need

See attached

but maybe you have not explained it properly, or I have not understood it

[#3]

Thanks for the reply Alan.

That doesn't work because it only sums the line items that are in that department.  I want the total sales of the entire transaction for every transaction that has at least 1 item in the department.

For department A I'm looking for a value of 217 (everything in transaction 1 and 4 because both of those have an item in department A) while using Sum(NetSales) will only give me 93 (54 + 39).

I hope this clarifies my question.

Regards

[#4]

Try something like this:

Sum(aggr(sum(NetSales), TransactionKey))

with Department as dimension

[#5]

Hi Claudio,

I appreciate the feedback but that doesn't produce the correct results.  I get this with your formula:

Department	Net Sales
	517
A	217
B	133
C	0
D	167
E	0
F	0

Thanks,

Dan

[rustyfishbones]

Hi Dan,

How do you expect to get Department A NetSales 217?

Are the NetSales not Summing to 93 based upon your example

[#7]

OK, i you should group by Department too,

sum(aggr(sum(NetSales),TransactionKey,Department))

[#8]

Alan,

I get 217 by adding up all 6 lines that belong to transaction 1 and 4.  What I need to do is find the total transaction net sales for every transaction that has at least 1 item in a given department.  So in department A's case there are two transactions that have an item in department A and those are transactions 1 and 4.  Therefore I want the total net sales for transaction 1 and 4 which is 217.

I don't want the 93 result because that only includes 2 lines (the one department A line from transaction 1 and the one department A line from transaction 4).  I need all 6 lines from the two transactions.

Thanks,

Dan

[#9]

Thanks again Claudio but that still didn't do the trick.  I get these results:

Department	Net Sales
	517
A	93
B	201
C	102
D	32
E	4
F	85

[rustyfishbones]

that will give the same result as SUM(NetSales)