Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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.
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.
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))
Thanks a lot! That's it!
Thanks too, also correct!