Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to sumproduct a function with an embedded sum aggr function but I cannot get the desired result. The function is as follows, with everything before the * being part A, and everything after being part B; how do I get the desired result of =sumproduct(A,B)? I have tried putting together a nested aggregation but the function is still producing unintended results.
(sum(Aggr(Sum({ <[Comp_Industry.Market],[Comp_Industry.Segment]>}[Comp_Industry.Relevant Sales])/
Sum({ <[Comp_Industry.Market],[Comp_Industry.Segment]>} [Comp_Industry.Total Segment])
,[Comp_Industry.Segment])))
*
(Sum([Sales.Segment Sales])/Sum({<Sales.Brand=,Sales.Nameplate,[Sales.Market]=>} [Relevant Industry]))
Thanks in advance for any help or guidance!
It is difficult to know what you have and what is needed by just looking at the expression, but may be try this
Sum(Aggr(
(Sum({<[Comp_Industry.Market], [Comp_Industry.Segment]>}[Comp_Industry.Relevant Sales])/
Sum({<[Comp_Industry.Market], [Comp_Industry.Segment]>} [Comp_Industry.Total Segment]))
*
(Sum([Sales.Segment Sales])/Sum({<Sales.Brand, Sales.Nameplate,[Sales.Market]>} [Relevant Industry]))
, [Comp_Industry.Segment]))
Thank you for the response Sunny! Unfortunately that expression provides the same results as the first piece of the equation. Does the function need to be formulated as "sum(aggr(sum(aggr"?
How can I provide more clarity in explaining my issue?
Thanks again for the help!
May be provide a sample?