Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a dataset that has a numeric count, the sum(sales) would have to be divided by the numeric count. There are other dimensions that I'd like to add to the expected result table, but for simplicity, I have parred down the table here.
Where multi-attribution is 1, that is its own group, everything that isn't one should be grouped together, but need to be divided by its own multi-attribution number.
Raw Data
MA_Desc | MultiAttribution | Store | Sales |
1:1 | 1 | A | 600 |
1:1 | 1 | A | 400 |
1:1 | 1 | B | 300 |
MultiAttrib | 2 | A | 800 |
MultiAttrib | 2 | A | 1200 |
MultiAttrib | 3 | B | 1500 |
MultiAttrib | 3 | B | 100 |
MultiAttrib | 3 | B | 300 |
MultiAttrib | 3 | A | 400 |
Expected Result (the math is in parenthesis)
MA_Desc | Store | Sales/MultiAttrib |
1:1 | A | $1000 (1000/1) |
1:1 | B | $300 (300/1) |
MultiAttrib | A | $1133.33 (2000/2 + 400/3) |
MultiAttrib | B | $633.33 (1900/3) |
Try this
Sum(Aggr(Sum(Sales)/MultiAttribution, MA_Desc, Store))
hi
use this expression
sum(aggr(sum(Sales)/MultiAttribution,MultiAttribution,Store,MA_Desc))
Thanks! I had the divisor field on the outside...