How do you handle non-additive measures in your data model?

[Seier-Alsenz]

Hi,

How do you handle non-additive measures in your data model?

In my data model I have various key figures. For example: Sales, personnel expenses, number of units sold.
The basic data is available on a monthly basis.

Now I also have the key figure "Number of employees" per month or also "Number of FTE". Per month.

I can sum up the turnover of the year 2023 month 1-9, but not the number of employees. For example, I have 200 employees (per month). Over 9 months, my evaluation would therefore result in 1800 employees, which is nonsense.

I somehow find it difficult to deal with the key figure "employees" in analyses. I regularly bump into tables, pivot, line charts or KPIs, especially when I want to evaluate variable time periods.

What can I do to help me deal with measures that cannot be added in the timeline?

[Mark_Little]

I normally in include them in my normal data model and just address with Set analysis something like

SUM(AGGR(MAX(Value),Month))

but if you prefer you could create a sperate table in your data model that is attached to your main data model, but capture at the correct aggregation level 

[vincent_ardiet_]

When you have to compute ratios, you need to have the numerator and the denominator in your datamodel. Then, you can have a dynamic result in your chart by doing there the division. For example, we have dashboards with percentile computation and so, we have all vectors of values in a table, and this is computed in the UI. 

[Or]

There isn't really a one-size-fits-all answer, because the required solution needs to fit the actual data model.

If your table has a month and the number of employees, the easiest way is to use FirstSortedValue(). If the number of employees needs to be summed per month (for example, the number of employees per department), you'll likely need to use Only(aggr(sum(employees,Month))) and avoid summing the total since you have no way of knowing how many distinct employees existed in the time frame. If you have a list of employees associated with the months they were employed, you might use count(distinct employeeID). Other solutions probably apply to scenarios not listed.