Crosstab report with Column Totals - One should be a calculation

[pammassey]

I have created a crosstab report that totals at the bottom of each column. Unfortunately, one of the columns is a percentage, so the total should actually calculate or allow customization instead of totaling. There is no option for that. I've read some of the posts out there that talk about using Solve Order. Unfortunately, that feature is not available on the field either. Any solutions? This is how my report looks:

Supplier Code    Supplier Name    Dept    GM%    PY MTD Sales    PY Inv    12MO Total    Sales    Inv Value
     #####         XXXXXXXXX      XXX    %.%%        $$$             $$$$          $$$$        $$$$       $$$$
Total                                                    %.%%        $$$             $$$$          $$$$        $$$$       $$$$

[AnalyticsWithJay]

Is this relational, DMR or OLAP data? Is the % Calculation a calculated data item in the report? Or is it a measure in the model?

What are the Aggregate Function and Rollup Aggregate function properties set to in the query?

Depending on the source, the data item, and the properties of the data item, we'll hopefully be able to answer your question. First, I would want to determine whether the incorrect value is a result of an incorrect aggregation property or if it's related to solve order.

[pammassey]

It is a relational database.  The data item is a calculation within the query.  The aggregate and rollup aggregate functions are both automatic.

[hespora]

I believe setting the rollup aggregate function to calculated rather than automatic should solve your problem. Possibly, you might have to set the aggregate function to calculated as well.

[pammassey]

No, setting the rollup aggregation to Calculated did not work.  Neither did setting up the aggregation to Calculated work.

[tjohnson3050]

In a Crosstab, the intersection aggregation is affected by both the aggregate property on the data item (column) and the crosstab space (row).  Check the aggregate property on the total row.  This will override the aggregate property on the data item.  If it is set to 'total', change that to 'aggregate'.  This will allow the aggregate property on the column be the one that determines the aggregation method on that intersection.

[pammassey]

Didn't work.  Changing the aggregate didn't work.  It just nulled out the total field for the % column altogether.