Highlighted
Occasional Contributor

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 

10 REPLIES 10
Certified Master Anaplanner

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

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

Certified Master Anaplanner

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

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.JPGGrid view.JPG

 

Occasional Contributor

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

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. 

Capture.PNGCapture.PNG
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

Occasional Contributor

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

Hi @AlejandroGomez

 

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. 

Capture.PNGCapture.PNG
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

Certified Master Anaplanner

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

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

Certified Master Anaplanner

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

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. 

Certified Master Anaplanner

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

Hi,

 

Please try the below logic,

 

Photo1.pngPhoto1.png

 

~Vignesh

Community Boss

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

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

Certified Master Anaplanner

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

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

 

@aabalos , try this way

Photo1.pngPhoto1.png

 

~Vignesh