Calculations using list items within a line items?

acarrara
Occasional Contributor

Calculations using list items within a line items?

I have a line item that needs to reference one of the list items instead of a line item.  What does this mean?  I have a list of staffing types that use a model configuration to calculate based on what the user selects.   Each staff type is mapped, then calculated off the drop selections the user picks.  The main one is the driver selection, this one is where it will chose the line item to do the calculation off of.  

 

To keep this simple...

 

I need to reference the value of a different staff type not a different line item.  How do I do this?

 

For example, WFM staff type selects Based on Clinical Non Call Center, in the calculation of this I want to reference trainer's value and other logic.  How do I do this since it is a list item and not a line item?

 

 

 

 

4 REPLIES 4
acarrara
Occasional Contributor

Re: Calculations using list items within a line items?

Including the photos I thought I included.

BCSFB
Certified Master Anaplanner

Re: Calculations using list items within a line items?

Hi @acarrara ,

Thank you for sharing your calculation problem with us.

In my first interpretation I thought I would have been able to help out.
Now that I have seen the screenshots I am back where I started.

Would you be able to clarify based on the screenshot what needs to happen.
In your example of the first post you mentioned a value for the trainer.
What item do you refer to by "value"? 

 

Cheers

acarrara
Occasional Contributor

Re: Calculations using list items within a line items?

Yes, let me try to explain better and use a better image.

 

Below is a list of staff types, they are listed in my configuration module.  User selects a driver for each staff type.

 

List Type Names.

Mobile Manager
EOC Manager
EOC Coordinator
EOC Supervisor
EOC Vendor Manager
Implementation (Project Manager)
Program Management
IT Project Manager

 

In image you will see select driver has options like Number of direct employees, based on site Divide. 

 

I need to create a new line item for the driver named "Whatever employees by location". 

 

In this logic it will say, when driver doesn't equal this driver <> 0, else do some math / logic.

 

Now say we map EOC Manager to "Whatever employees by location" and it is just the value of:

 

If select driver <> Whatever employees by location

then 0

else EOC Coordinator * # of locations.

 

How do I enter EOC coordinator list item into this logic?

 

acarrara
Occasional Contributor

Re: Calculations using list items within a line items?

We found a solution for this.

 

1. To do so, in our formula we reference the line item that we mapped to the specific staffing type.

2. Then we added a new column to the driver model that then references the staffing type.

3. This is where we did two things and both worked.

First we referenced the formula as mentioned in #1 above and first we did a lookup to the new column.

Then we realized we needed to reference multiple staff types, so we changed it to a SUM function and reversed the order of the mapping in #2.

 

('Based on Per Customer'[SUM: 'Setup: Internal Staff Driver Model'.Sum Mapping] +

Based on Call Volume[SUM: 'Setup: Internal Staff Driver Model'.Sum Mapping]) / '1 Per:'