Assuming you are using a pivot table?
You don't need to use the SUM() function - you can just create a column that is column A + Column B
For example - If Column A = TOTAL_GROSS_MARGIN and Column B = TOTAL_PT, in the pivot widget design you can use the function builder to create a 3rd column (value) that is TOTAL_GROSS_MARGIN + TOTAL_PT which would be the sum of Columns A and B
alternatively - you could also create a calculated column in the data model that is Column A + Column B and then reference that new field in the pivot table design
Hope this helps
Thanks for the response. I have a couple of follow-up questions.
1) I've used the strategy that you're suggesting of using simple math in a formula (col A + col B), but doing so requires an aggregation of the column (i.e., [min of col A] + [min of col B]). I don't love that solution (we embed in our own app and I am concerned about user confusion with this strategy.
2) I've tried the strategy you're using, as pictured, where you're using [Total of col A] + [Total of col B]. The problem with using the Total aggregation is that it ends up doubling values if the pivot table is aggregating multiple rows. Hard to explain via text, but maybe that makes sense?
3) I'd still like to see an example of how SUM() works.
Hi bminehart ,
From what you explain, I think the issues you are having are from using a numeric value as a dimension.
It is legitimate in some situations, but can bring some confusion to the end user.
In this situation, you wouldn't represent those values as a sum, but you would be adding that column to columns and/or rows panels in the pivot.
By adding those columns to the values panel, the pivot will treat them as measures, and will add the values up, thus multiplying values when aggregating multiple rows.
This does not really have to do with the fact that you are doing a calculation with those two variables, but rather with this situation of dealing with a numeric dimension.
As psdutton315 , one solution would be to do the addition on the data model itself, and creating another numeric dimension, that you would then be able to add in the rows/columns panel.
When doing that addition on the pivot, rather that using the minimum aggregation, I personally prefer using the average aggregation, although it will return the same result.
As of the sum() function, it will simply add all the values, and writing sum([col A]) ends up being the same as [Total of col A].
Please let me know if this helps : )
Always here to help,
Helena from QBeeQ
[email protected]
QBeeQ - Gold Implementation and Development Partner
