[AI - Generated post but still good]
**
The problem**
When building Excel reports with Slice, it’s common to use Virtual Consolidations (#) to create ad-hoc rollups across dimensions. For example:
- Region:
#[EMEA]+[APAC]+[LATAM] - Product:
#[Product A]+[Product B]+[Product C]
This works perfectly for additive measures — Revenue, Cost, Units, etc. The # syntax sums the leaf cell values client-side and the result is correct.
However, if your report also includes ratio/KPI measures defined by cube rules (e.g. Cost / Revenue as a margin %), the virtual consolidation row will show an incorrect value — it simply sums the individual percentages instead of recalculating the ratio from the aggregated numerator and denominator.
Example:
- Product A: Revenue $500M, Cost $50M, Cost % = 10.0%
- Product B: Revenue $300M, Cost $18M, Cost % = 6.0%
- #[Product A]+[Product B]: Revenue $800M
, Cost $68M , Cost % = 16.0% 
The 16.0% is just 10.0% + 6.0% — a meaningless sum of percentages. The correct answer is $68M / $800M = 8.5%.
The root cause
The # virtual consolidation is evaluated client-side by Slice in Excel. It fetches each leaf cell value and sums them. For ratio cells that are already computed by cube rules (e.g. ['Cost'] / ['Revenue']), the stored value at the leaf level is a percentage — and summing percentages doesn’t produce the correct consolidated ratio.
The fix: use ##AGGREGATE instead of #
Replace every # virtual consolidation with ##AGGREGATE on all dimensions where you use a virtual rollup. This sends the aggregation request server-side, where the TM1 engine correctly evaluates the cube rules at the consolidated level.
Before (broken for ratios):
- Region:
#[EMEA]+[APAC]+[LATAM] - Product:
#[Product A]+[Product B] - Measure (ratio):
#[Cost]/[Revenue]
After (correct):
- Region:
##AGGREGATE({[Region Dim].[EMEA],[Region Dim].[APAC],[Region Dim].[LATAM]}) - Product:
##AGGREGATE({[Product Dim].[Product A],[Product Dim].[Product B]}) - Measure (ratio):
#[Cost]/[Revenue]— no change needed here
Important: ALL dimensions must use ##AGGREGATE
This is the key insight that’s easy to miss. If even one dimension still uses the # syntax, Slice falls back to client-side summing and the ratios will be wrong.
In my case, I initially only converted the Product dimension to ##AGGREGATE and still got incorrect results. The Region dimension also had a # virtual consolidation sitting above the calculation area that I had overlooked. Once both dimensions were converted to ##AGGREGATE, the ratio came out correct.
Why this works
#= Slice fetches each leaf intersection, sums the values in Excel → percentages get summed (wrong)##AGGREGATE= Slice sends an MDX AGGREGATE() to the TM1 server → the server aggregates the $ measures first, then the cube rule evaluates the ratio from the correct totals (right)
The ratio dimension itself
For the measure/line-item dimension where you define the ratio (e.g. #[Cost]/[Revenue]), you can keep it as #[A]/[B] — it works correctly as long as the other dimensions use ##AGGREGATE. The critical factor is that all non-ratio dimensions with virtual consolidations use ##AGGREGATE.
Summary
#[A]+[B]+[C](client-side): Additive measures | Ratio/KPI measures (sums percentages)##AGGREGATE({...})(server-side): Additive measures | Ratio/KPI measures (cube rules fire)
Hope this saves someone the debugging time. Cheers!