Skip to main content

I have a source metric ABC with dimensions: project and year of data type: number

need to have a target metric with dimension year, current formula in target metric is :
SUMOF(ABC)/COUNTOF(ABC), but i want year as a dimension too in target metric.

currently, it doesnt have any dimension in the target metric.
 

Hi ​@pigment_enthusiast,

Are you looking for the average of Metric ABC by year?

If so, try this formula:

ABCBREMOVE SUM:Project] / ABCBREMOVE COUNT:Project]

Explanation:

  • ABCBREMOVE SUM:Project]:
    This calculates the sum of ABC values while removing the Project dimension but keeping the Year dimension. Note that SUM is optional here.

  • ABCBREMOVE COUNT:Project]:
    This counts the number of non-blank ABC values, removing only the Project dimension. It effectively counts the number of projects for each year.

Using this approach, the numerator gives the total ABC per year, and the denominator gives the count of projects per year, resulting in the average.

Regarding, what you had :

  • SUMOF(ABC) calculates the sum of the entire ABC block but remove all the dimensions.
  • COUNTOF(ABC) retrieves the count of all non blank cells in the ABC block, it also removes all the dimensions.

Hope this helps,

Reply


Ask your question here!

Terms & ConditionsCookie settings