Full value for all orders containing dimension value

[jbhappysocks]

Hello

I am working with Campaign analysis. The Campaign dimension is tied to the Item, not to the transaction. So an invoice can contain 1 Item purchased on Campaign A, 1 Item on Campaign B and a 3rd Item purchased without any Campaign tag.

What I want to achieve is a list of all Campaigns and the total Order Value for all invoices containing at least 1 Item tagged with every Campaign. So the example with Campaign A,B and no tag would give the same value for all three rows if only that invoice is selected.

Load * inline [
Invoice, Item, Sales
1, A, 100
1, B, 100
1, C, 100
2, B, 100
3, B, 100
3, C, 100
];

Item:
Load * Inline [
Item, Campaign
A,Summer
B,Winter
C,Spring]
;

Should result in 

Winter 600 (total invoice 1,2,3)

Spring 500 (total invoices 1,3)

Summer 300 (total invoice 1)

Anyone? 🙂

[GaryGiles]

I haven't found a way to do this via an expression given your data model.  However, if you add the following code to your load script:

Join (Invoice)
Load Invoice,
sum(Sales) as Invoice_Total
Resident Invoice
Group by Invoice;

You can easily get what your are looking for by using Campaign as your dimension and sum(Invoice_Total) as your measure.  This assumes that your first table is named Invoice.

[jbhappysocks]

Hi.

Thanks for your reply. But this assumes no Items on an invoice ever belongs to the same campaign. It would work with the example I provided, but if Item A also belonged to Winter the total for Winter should still be 600, but a sum(Invoice_Total) would give 900 since Invoice 1 would be counted twice. 

[GaryGiles · Accepted solution]

With Campaign as your dimension, use this as your measure expression:

sum(aggr(avg(Invoice_Total), Campaign,Invoice))

[jbhappysocks · Accepted solution]

Thank you, you solved the example I provided.

Unfortunately I realized when trying it out the example I gave was a bit oversimplified, I specified Full Order Value for all orders containing dimension. It should have been Order Value for current selections. I still needed to be able to to make selections in other Product dimensions.

But your solution led me in the right way and I'm using this now, kind of doing the same but in the expression instead 🙂

sum(aggr(sum(total <[Invoice]> {<Campaign=>} Sales),Campaign, Invoice))

Seems to work as I want it to. Thanks for the support.