What are Metric accounts and when to use them?

 I am often asked: "What are Metric accounts in Adaptive and what is their use case?"


Before we jump into the weeds, you can create "global" metric accounts, called standard metric accounts and within cube sheets. 

To create a standard metric account, go to:  Menu > Modeling > Metric Accounts



Adaptive will come with a couple of pre-built Metric accounts, such as Gross Margin %


What are Metric Accounts?

A metric account in Adaptive is a calculated account, meaning it carries a formula. 
It does not carry any data entry. 
It can not be a rollup account, meaning it can not have any "children accounts" rolling up to a parent.

Metric accounts are typically used to calculate ratios, this is why they can only be either a number or a percent! But... there is more subtility in them!




Metric accounts are different from the other calculated accounts (custom, modeled, cube etc.) in the sense that their formula is also computed at the level and dimension rollups! 


Example on how it really works

Let's take Gross Profit and Gross Margin as examples.

  • Gross Profit $
    • is a currency value. 
    • the rollup (fiscal year) is a sum of the children (quarters)
    • This will be a standard custom account.
  • Gross Margin %
    • is percentage value.
    • the rollup (fiscal year) is NOT a sum of the children (quarters). 
      • the division has to happen at the fiscal year rollup: total Gross Profit for the year divided by the total Revenue for the year. 
    • This will be a metric account!

Gross Profit  = Revenue  -  Cost of Goods Sold

Gross Margin = Gross Profit / Revenue


  Q1 Q2 Q3 Q4 Fiscal Year
Revenue $ 1,000 $ 3,000 $ 2,000 $ 4,000 $ 10,000
COGS $ 500 $ 2,500 $ 1,500 $ 2,000 $ 6,500
Gross Profit $ 500 $ 500 $ 500 $ 2,000 $ 3,500
Gross Margin 50 % 17 % 25 % 50 %35 %


In this example, we clearly see that the Gross Profit $ at the end of the Fiscal Year is the sum of the quarters (= children): 500 + 500 + 500 + 2000 = $ 3,500. This can be achieved via a formula in a standard account.

But Gross Margin, at the end of the year does not equal the sum of each quarter (50+17+25+50 = 142%). The rollups of Revenue and Gross Profit must be used: 3500 / 10000 = 0.35. 
The math needs to happen again at the fiscal year rollup. And this can be done via metric accounts!

Test it yourself: Try writing that formula in a standard custom account (non metric) and then in a metric account. Compare the 2! You will see that for the standard custom account, Gross Margin at the end of the Fiscal Year will be the sum of the quarters! Not good!
divf(ACCT.GrossProfit, ACCT.Revenue)
 
In Workday Adaptive, metric accounts do the calculation at the dimension rollups and time rollups!

Another intricacy of metric accounts is that the calculation does happen at ALL the intersections of data (levels + dimensions + time), whether there is data or not... So it can be heavy on the computation. Use them sparingly... Metric accounts are typically only used for ratios calculations. Don't use a metric account, when in reality it should be a standard one!

❗Limitations

Time Modifiers can not be used in metric accounts.
For example, you can't write something like this:  ACCT.m_GrossMargin[time = this - 1]
More info about Time Modifier in Metric account in this link.

So if you need to write a Year Over Year increase or decrease of your Gross Margin, it is a 2 step setup in Adaptive. Another article will come on that topic.


💡Best Practice

When you write a metric account, it is a good practice to add a prefix, such as "m_" in the code.
This way, when you reference it in other formulas... you immediately know that it is a metric account!







 

Comments

Post a Comment

Popular posts from this blog

Adaptive REST API Calls for Platform Customers with User Sync Enabled

How to Make Adaptive REST API Calls for Platform Customers with User Sync Enabled If you’re a platform customer (HCM + FINS + Adaptive) or using Workday HCM or Financials alongside Adaptive and want to create custom scripts leveraging Adaptive’s REST API (XML), you’ve likely encountered a key requirement: providing Adaptive user credentials (username and password). For standalone customers or those not using user sync, this typically involves creating an integration user in Adaptive. Easy peasy.  But what if you’re using user sync? How do you supply credentials for API calls in the XML input? In this article, I’ll guide you through the steps to generate the necessary credentials when user sync is enabled. I’ll also share helpful links to official documentation to ensure a smooth integration process. 1. Overview The process allows Workday-synchronized users to make API calls to Adaptive Planning using Workday credentials . The key steps involve: Generating an OAuth token using Work...
Read more

Introduction to Adaptive's API

To integrate data in Workday Adaptive Planning, customers typically use the integration module to connect with ERPs, SFTPs, AWS S3, Snowflake, SalesForce, Excel, and Google Sheets, etc.  You can also use third party platforms such as Tray.ai , Boomi , Matilion etc. which leverage Adaptive's APIs in a user friendly setup.  And lastly, you can write your very own scripts (C#, Python, Powershell...) to make these API calls.  Most  common Adaptive's API use cases Import Data from ERPs Actual summaries Actual transactions Assets, amortization, depreciation Transactional details by Supplier Payroll details and taxes Import Sales data from CRMs or datalakes Pipeline, churn, opportunities and customer list Billings and bookings ARR and MRR Import Headcount data from an HRIS Current headcount Terminated Hired, not started Wages and compensation plans Benefits Other Demographics Import Other data: Exchange Rates Weather Import Meta data: currencies, attributes, dimensions such...
Read more

What is OAuth 2.0 and how to set it up in Adaptive?

Image
Uh Oh... OAuth? Was ist das? Adaptive can connect with other applications's via REST APIs using the OAuth 2.0 authentication.  I will explain what OAUTH is so you will no longer ask: "What is this?" (Was ist das?) and feel like someone is talking a foreign language to you!   What is OAUTH 2 really? How does it work and what do you need to do in Adaptive? Let me give you 2 sets of explanations: a real life illustration to demistify OAuth 2.0 and a more technical summary. And lastly high level steps on the setup in Adaptive. If you have no idea what OAUTH is, continue reading, otherwise jump to the 2nd section.  In this example: imagine you need to connect to Microsoft Sharepoint and you want to bring CSV files  into Adaptive. 1) Illustrational explanation for total newbs: OAuth Access vs Hotel Access! OAuth 2.0 can sound overwhelming at first, but it's essentially a way for applications to securely talk to each other and access data. Let me break it down into sim...
Read more