Level 1 Exam - Question 5

Need to update the formula in EMP02 with EMP03's info. But I keep getting an error and I have no idea where I have gone wrong... Can anyone point me at the right direction?

This is the formula I used...

IF Employed? THEN Salary *'EMP03 Employee Drivers by Role'.Bonus %[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Role] ELSE 0

But getting this error?

Invalid formula for 'EMP02 Employee Expenses'.Bonus: Automatic sum of 'EMP03 Employee Drivers by Role'.Bonus % over E3 Role hierarchy is not possible as E3 Role does not have a built-in top level.

I have been on it for hours! Any help is appreciated

Thanks

Tony

Comments

anand.shekhawat

@TLam99

Possibly the top level is missing in your 'Roles' List, you need to create the same.

~Anand

cuneydyasin

This doesn't work unfortunately.

Plus "All Roles" is already at the top level in this list:

cuneydyasin

I solved this by deleting "All Roles" and inserting this line back as the parent.

The problem continues though. This time it says:

cuneydyasin

Now I'm trying to change the format of Roles in SYS08, from text to list. But it gives this error:

Format of mapping used for lookup doesn't match any dimension of the source.

gaurav_sharma-bng

Hi @TLam99 and @cuneydyasin ,

I guess this could be a dimensionality issue. Let me share screenshots of a working formula and please check the dimensions in your model and the structure. You don't need 'All Role' here because you are searching for a Bonus percentage for each employee based on the department and the role of that employee.

SYS08 module - Dimensioned by Employee

EMP03 - Dimensioned by Role and Department and has Bonus% as line item

EMP02 - Uses the shown formula to fetch the Bonus% and multiply with existing salary of employee

Let me know in case of any concerns.

Regards,

Gaurav

cuneydyasin

Thanks for the response..

We need "All Roles" not because its calculations will be used in any cell, but because the list could not be referred by the formula in the case of a non-existent parent.

It seems that your "Role" column in SYS08 is formatted as a list, since you can choose a value from a dropdown menu. This is the issue for me actually: I can't change it from text to list (by referring to the Roles list). When I try, it gives this error: "Format of mapping used for lookup doesn't match any dimension of the source."

gaurav_sharma-bng

@cuneydyasin - Can you check the reference of the 'Role' line item in SYS08 module. It must be getting referred to calculated another line item in a module. You would first need to remove that formula to make the 'Referenced by' blank and then change the line item format to 'Role' list

cuneydyasin

Wow that worked. Thanks!

cuneydyasin

I just wanted to write this as well:

My solution was creating a Role2 column and re-importing the data for only this column and formatting it as a list. I would refer to this column in EMP02 formulas.

gaurav_sharma-bng

@cuneydyasin - pls mark it as a solution so that others can refer to it as well.

Regards,

Gaurav

Quick Links

Recent release news

May 2025 platform releases and what’s next
Check out the latest Anaplan feature updates and enhancements in our May 2025 official release notes. The information below offers supplemental information to that post. Integrations and extensibility Preview dataset when mapping to model or selecting for transformation view in ADO When selecting a dataset to map to a…
April 2025 platform releases and what’s next
Check out the latest Anaplan feature updates and enhancements in our April 2025 official release notes. The information below offers supplemental information to that post. UX New UX for Models section in Administration The Models section in Administration has been redesigned with a more intuitive interface. Tenant…