Average Market

Hello,

I have a list of 200 companies with all their P&L numbers in a module. Now I want to sum all the data of the 6 biggest of these companies into one artificial company to have a market average.

How do I sum the 6 companies that I chose into a company called "benchmark Market" and then compare any single company to that "created benchmark company"?

The choice of the companies has to be changeable, preferably with a boolean on the list "companies"

Thank you!

Kind regards,

Filip

Accepted answers

All comments

billdowling

Hi Filip, there are a few ways you could do this. You could use RANK to test for the top 6 companies, but if you want the companies to be changeable, I would sort the output and use a Boolean to select the companies you want to include in the benchmark.

You can then store the benchmark average in a separate module and reference this when comparing a company to benchmark.

Below are some screenshots of a simple example. You could also dimension this over time to track the benchmark history.

Bill

derektslim

Hello Filip

That is a fun and interesting question you have. As Bill suggested, there are a few ways to achieve this and my approach is similar.

I would set up the module as follows:

Check for Comparison? : Boolean, Summary None - Allows user to choose Company to compare
Revenue : Number, Summary None - I assume you will have this formulated somehow
Comparison Revenue : Number, Summary None - Gets revenue if selected for comparison
IF Check for Comparison? THEN Revenue ELSE 0
Comparison Ranking : Number, Summary Max - Ranks comparison revenue accordingly
RANK(Comparison Revenue)
Top # Revenue : Number, Summary Sum - Gets the top # revenue according to input (user input, see second module), and adds in place a check in case there is lesser companies ticked off
IF 'MOD2 Selector'.Top # Ranking for Comparison < Comparison Ranking[SELECT: 'L1 Company List'.Total] THEN IF Comparison Ranking > 'MOD2 Selector'.Top # Ranking for Comparison THEN 0 ELSE Comparison Revenue ELSE Comparison Revenue
Top # Revenue Average : Number, Summary None (No Dimension)
IF 'MOD2 Selector'.Top # Ranking for Comparison < Comparison Ranking[SELECT: 'L1 Company List'.Total] THEN Top # Revenue[SELECT: 'L1 Company List'.Total] / 'MOD2 Selector'.Top # Ranking for Comparison ELSE Top # Revenue[SELECT: 'L1 Company List'.Total] / Comparison Ranking[SELECT: 'L1 Company List'.Total]
Variance from Top # Revenue Average : Number, Summary None
IF Check for Comparison? THEN Comparison Revenue - Top # Revenue Average ELSE 0

A module that allows user to input the top number to be used for comparison

Hope this helps!

Derek Lim

filipsneyers

Thanks for all the answers, but the next problem is already showing up:

I now have 2 modules with multiple line items (that are KPI's that are calculated upon all that P&L data per company).

Module 1 "Cal01 KPI's" with the dimension "company" that already existed, so I have all the KPI's per company in that module. I made a Line item subset of all these KPI's - LIS Finances

Module 2 "Cal01b KPI's - Benchmark Company" without the dimension "company" , but which contains all the KPI's of the Benchmark company. I also made a Line Item subset of the same KPI's as the module above which is called LIS Finances BM

To link these 2 line item subsets(because the KPI's are the same) I made a mapping table (sys TEST) between LIS Finances and LIS Finances BM.

Problem is that I cannot make a module to compare any company of module 1 to the benchmark company of module 2. The problem is that the first module has the "company" dimension" and the 2nd module has NOT.

Can anyone help me how to have the same KPI's in one module with no subsidiairy views so I can compare any company with the benchmark company and show it on a graph on an app page?

I will share a view of module 1 and a view of module 2.

I already made some attempts in the images "solution that does not work". Problem is that the company is ok but that there are no values for the benchmark company (always 0,0%)

Thanks for the help.

Quick Links

Can you help?

Unanswered questions

Missing Column Headers in Anaplan Export When Using API
Hi Community, I’m encountering an issue where an export created in Anaplan is missing column headers when retrieved via the API. However, when I manually run the same export from the Actions tab, the downloaded file includes the headers. Has anyone experienced this behavior before? I’d appreciate any insights into the root…
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