How to aggregate percentages ?

[jcoira]

Hi,

I have a FACT table with measures like showed above

TotalQuantity Percentage ParcialQuantity
(calculated member)
100 1% 1
200 2% 4
100 3% 3
--------- -------- ----
400 6% (wrong!!) 8

I would like to know how to tell Analysis Services to not aggregate the percentage facts with the defualt SUM function(because it would lead to wrong calculations, as you can see on the table above). Instead I need to calculate the percentage with a formula like Percentage=(ParcialQuantity/TotalQuantity)*100.

Any clue ?

Thank you.

// jcoira

 

[blom0344]

If I translate this situation to by BI tool (Business Objects) this issue is possibly due to a design decision. In many cases a percentage calculation is a division within a total , which will result in adding up the percentages to 100% (always). This is desirable, cause it confirms correct percentage calculation. In your case this automatism is undesirable.
On the other side , having percentages as values in a fact table is questionable, cause a percentage will often not be calculated at recordlevel. You often work with a definition of a range within the table to base the calculation upon. This logic will not be apparent when just looking at the table contents. It is better to define these calculations at report level.............. T. Blom
Information analyst
tbl@shimano-eu.com

 

[NaughtyVicar]

If you are using MicroStrategy, go to the metrics editor/advanced tab and click the checkbox for "Smart Totals". This forces the query engine to work from the base totals instead of simply adding up the column.

-Rich