How to sum a column by looking up a dimension in the module

Hi,

I have this module that has a dimension list called Sales Title and a list called Quota Types. I have a line item called SSA Revenue, and it is 1 if it has SSA Revenue, else 0. I have a second line item and I want to sum the total number, grouped by Sales Title. Picture attached. Would I be able to get some help please? 🙂

Thanks!

Amanda

Capture_42085.PNG

Accepted answers

All comments

kavinkumar

HI Amanda,

Use Select function to get the total. For ex. SSA Revenue Counter[select:Sales Title.Total] where it will return the total of the Sales title list.

Hope this helps 🙂

Thanks,

Kavin

AlejandroGomez

Hi Amanda,

try to do the following:

1) create a new Line Item (LI) in your module formatted as number.

2) make sure that the summary method for this new LI is SUM

2) change the dimensionality of that LI (applies to field) and select only the list "Sales Title"

3) I assume that you already have a LI formatted as List: Sales Title in your current module

4) In the new line item, select the SSA Counter Li and sum it by the LI with Sales Title List format.

5) now you would be able to see the sum per Sales Title

6) Make sure that both lists you are using as dimensions of your module have a built-in top member (you can easily check and amend this at the configuration tab of the list)

See the pictures attached for ruther details (List with Items named "Sx" is your Sales Titles and the lsit with items named "Qx" is your Quota Type list)

Cheers,

Alex.

More knowledge at https://olivehorse.com/

Blueprint view.JPG Grid view.JPG

aabalos

Hi Alejandro,

Thank you! Your solution is the exact solution im looking for! However, when I do this in my module, it returns the sum of the whole line item instead of the sum of each sales title, see picture attached.

In this module, the original pivot is Sales Title as a page, Quota Types as row, and line items as columns. In this current view, i have repivoted it to Sales Title and Quota Types as row and line items as columns. I'm not sure if that is making any difference. Would you know how to fix this?

Thanks!

Amanda

aabalos

Hi @AlejandroGomez,

Thanks!

Amanda

Rashmih

Hi ,

@aabalos

Could you share the details of line item "Sales Title". . Is there a top level for the list "Quota Types" here...

Thanks and regards,

Rashmi

AlejandroGomez

I agree, the issue could be that the list being used in the module do not have a build-in top member item. @aabalos plese make sure of that at the configuration tap of the list.

VIGNESH.M

Hi,

Please try the below logic,

~Vignesh

DavidSmith

It is best practice to split SUMs and LOOKUPs, as well as avoiding subsidiary views

Create a separate module to do the SUMs and then have the LOOKUP back in the target module

This is much more efficient from a calculation perspective

David

VIGNESH.M

Yes David, I totally agree with your suggestion.Its best practiceto do sum in seperate line item.

@aabalos , try this way

~Vignesh

aabalos

Hi Veignesh,

I tried this method and I'm still getting the total sum of the line item.

Quota Types list does have a top level item. Is it because in my module, the quota types are specific to the sales title? It doesn't list all the Quota Types in the Quota Types list, it just shows the quota types that the sales title holds.

Quick Links

Can you help?

Unanswered questions

OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled
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.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385