Sum of Mix Aggregated by Product Presentation

[kira_whopper]

Hi there,

I need to calculate a KPI called Mix for each Product specific Presentation (every Product has one or more Presentations) and Sum the result for the Product (but still aggregated by Presentation).

The Formula is:
[ Presentation Unit Sales -
{ Product Unit Sales x

(Previous Presentation Unit Sales / Previous Product Unit Sales)
}
]

x
Previous Average Price -> Previous Presentation Sales / Previous Presentation Unit Sales

While showing the results by Presentation, I get the desired value, but when showing by Product, the result is 0

I'm sending attached a qvw file with to straight tables, one with the Presentation as a dimension (which gives the proper results for Product Mix only when Summing the rows for the total) and onde with Product as a dimension (which gives 0 as result for the Mix)

Also, I'm sending an excel worksheet with the data used in the QVW file and a tab named: "Specific Product", which has the desired result for the Mix in bold red on cell I6.

Thanks a lot, in advance...

[swuehl · Accepted solution]

Maybe like attached, using advanced aggregation to calculate the sum-of-rows.

I think there should be an easier expression to calculate the same, but it seems it's too late right now for me.

[sunny_talwar]

Try this:

Sum(Aggr((Sum([Unit Sales]) - (Sum(TOTAL <Product>[Unit Sales]) * (Sum([Previous Unit Sales])/Sum(TOTAL <Product> [Previous Unit Sales])))) * (Sum([Previous Sales])/Sum([Previous Unit Sales])), Product, Presentation))

[kira_whopper]

Thanks a lot! That's it!

[kira_whopper]

Thanks too, also correct!