Monday, January 28, 2013 4:50 PM
I have a cube that has a price point dimension, solution dimension, solution cateogry dimension and fact table that has list price.
Within the price point dimension I have quote_nbr attribute and a hierarchy called PricePts defined with one level Product Grouping.
Solution dimension has Financial Hierarchy with levels LOB > Offer Group > Adv Solution
Currently the cube is summing up list price.
I need it to average the list price based on how many product groupings there are and need it to do this regardless of where I am in the cube.
quote nbr product grouping list price
111111 selfserver 22 125.23
selfserver 25 150.22
selfserver 26 160.00
What I want is whenever I don't have product grouping listed the list price to be an average. So I should see
quote nbr list price
I have been trying to do this with scope but am having problems
Monday, January 28, 2013 8:23 PMAnswerer
you would usually create 2 physical measures, one with SUM and one with COUNT as AggregationFunction
then you would just divide SUM by COUNT in a calculated measure to get the average
this works for all scenarios
- www.pmOne.com -
- Proposed As Answer by Elvis LongMicrosoft Contingent Staff, Moderator Wednesday, January 30, 2013 8:49 AM