SUM results not matching up to summary

I'm having an issue with SUM giving me unexpected results.

When I'm working with hierarchy lists and just need a top level sum rather than subtotals at all levels of the hierarchy, sometimes I'll use a 1-item list, add a line item set to this list item, and SUM over it to get the top level sum. It's a little convoluted but saves space and calculations vs. turning on summary over all the intermediate hierarchy levels.

However, I just tried this and got a different number from the line item summary. While the line item summary gives me 276 at the top level (which is correct,) using the SUM function over my helper list item gives me 282.

I can't figure out why this is happening. Is there something I'm missing/forgetting about using SUM in this way?

Accepted answers

All comments

rob_marshall

@helennie

Can you share some screenshots demonstrating this?

helennie

@rob_marshall Thanks for the reply. I've added screenshots, including the formulas.

The top module's dimension is "Rep X Role"

The lower left module is dimensionalized by Users + "Mapping List" and uses the SUM function, while lower right module is dimensionalized by "Users" and is pulling in the top level from the line item summary.

rob_marshall

@helennie

Ok, it looks like you are doing it correctly and the numbers are correct except for IY02, so it must be somewhere in the data. Could you be summing data (bottom left box) where it is not valid?

Not that you asked this, but since you did share the blueprint (yea, I know, I asked 🙂 ), you may want to consider using a line item subset so you are not repeating the line item formula so many times and so it can potentially scale if you ever create more IY filters.

Rob

helennie

@rob_marshall what do you mean about data not being valid? Since I'm using the same line item in both cases, I assumed that any data issues that affected one way would affect the other, as long as the logic is correct.

Thanks for the tip about LIS - this is a "quick and dirty" proof of concept rework of an existing build. We will definitely clean it up once we are confident it's working correctly.

rob_marshall

@helennie

I am thinking, and again without seeing the data, I am wondering if you are summing data for IY02 that you shouldn't which could be throwing off the count. Since the top module has Users (the mapping line item), I am wondering if it is mapping a user to a value that shouldn't be there. It would way easier to find if the number was not in the hundreds, but I would look at the bottom left module and make sure all 284 are "valid". That is where I would start.

Rob

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…