I have a three dimensions among others, one called Manufacturer, Brand and a Time dimension with many special categories such as current month, prior month, YTD etc. I have in my model a single measure representing units sold.
Manufacturer A current month Market Share as %
Brand 1 1000 units 1000/3500
Brand 2 2000 units 2000/3500
Brand 3 500 units 500/3500
total for Manufacturer A 3500 units
How can I achieve this in the model itself. If I group by say Region instead of Manufacturer then Market share should be calculated using base totals for Region instead of Manufacturer. I have currently achieved this using powerplay calculations but for some reason have to incorporate in the model itself. Had no luck with documentation.
Please let me know if anyone has any suggestions.
thx
Why do you need to put it in the model?
in the example, I have indicated only one market share calculation for the sake of simplicity, but in the actual application, there are several such percentage to base calculations on different time categories. Each of these calculations have been incorporated in powerplay using different views of the cube published in upfront in production. On a normal day when the cube is rebuilt we have no problems, but if/when there is even a slight modification in the model design, all the calculations in the views are dropped and have to be recreated, which can be a recurring development overhead on this application not to mention a possible source of error while re-implementing the calculation. Instead, if the calculations are incorporated in the model itself, this issues are eliminated.
Is it possible to achieve this in the model?
thx