Crosstab subaverage issue

[Jozef De Geiter]

Hi all,

I'm having a subaverage issue in a crosstab with following specs:
               
- rows: productgroup - > product (nested)
- columns: 2 measures:
   * [Count Total Product Sales]: will count how often a product sales was initiated in a particular month
   * [Count Closed Product Sales]: will count how often a product sales was initiated & finalized in a particular month. For each sales, we know whether its status (open/closed) by means of a flag (1/0)

Hereunder you will find the example used to demonstrate my issue:

               Prod Sales  Closed Prod Sales      % Closed
Product A     10                 10                        100%
Product B     20                 20                        100%
Product C     60                 30                        50%
Product D     15                 15                        100%
AVG(Prod)    26,25            18,75                    87,5%


As you can see from these average values, they are based upon the visible product aggregates. Now, the purpose is to have an average for each product that is based upon the atomic values and NOT on the product aggregates, i.e.

75 (10+20+30+15) closed products sales / 105  (10+20+60+15) products sales = 71,4%

Instead of

(100%+100%+50%+100%)/4= 87,5%

Therefore, I had tried following aggregate calculation:
total([Count Closed Product Sales] within detail [Product]) / total([Count Total Product Sales] within detail [[Product])

However, when executing the report, I run into following error:
"The dimensions on the edge are inconsistent. The dimension of valueSet="3" differs from the dimension of sibling edgeGroups."

When I only take the nominator/denominator of the ratio, i.e. total([Count Closed Product Sales] within detail [Product]) OR total([Count Total Product Sales] within detail [[Product]), I get the expected values for the nominator/denominator... Also, when I create 2 distinct data items - each one separately containing the nominator & denominator - and afterwards take the ratio of these individual calculation, this did not work either...

I'm really very curious about the solution...

Thanks in advance

[167505]

have you tried using the within set ..

total([measure1] within set [products]) name it as numerator
total([measure2] within set [products])  name it as denominator

create one more dataitem and try to divide it.you have to set the solve order for numerator and denominator as 1 and for the avg , you have to set the solve order as 2..

if total doesnt work, then  try aggregate([measure1] within set [products])

how are you using the products, whether you are dragging single member for each product and place them one below each other or are you using set([Product A],[Product B])

Regards,
Nag

[PRIT AMRIT]

As you can see from these average values, they are based upon the visible product aggregates. Now, the purpose is to have an average for each product that is based upon the atomic values and NOT on the product aggregates, i.e.

75 (10+20+30+15) closed products sales / 105  (10+20+60+15) products sales = 71,4%

Instead of

(100%+100%+50%+100%)/4= 87,5%

All you have to do is, select the '% Closed' column in your cross-tab, in Properties, define solver order = 2.

This should get you desired result.

Thanks

[Jozef De Geiter]

Setting the solve order for the % calculation to 2 indeed resolved the issue that I was experiencing...

Many thanks for the replies!