During one of our International Diver Developer meetings recently, my colleague Hannes Hellsborn shared this simple yet effective solution for that niggly issue when you try to calculate a true average at any data level above the lowest transaction level, or row level in the data. Hannes works for Infotool Sweden (another Diver Distributor and Member of the Diver BI Group). I’m sure you will find this as useful as we did.
You can download this pdf if you prefer to keep a copy to hand. WeightedAverages
Unweighting weighted averages
Calculating an average is very straightforward for a transaction average i.e. an average representing a summary on the lowest level in the data set.
When the summary is represented on another level than the transaction level things get a little less straightforward.
By combining a standard transaction average with ProDiver’s DimAverage it is easy to bring the average to the correct level in the data.
In an HR model we want to calculate the average age of the employees. We use Age as a summary and will initially try to divide the Age summary by the number of rows.
In the model we also have EmploymentID. An employee can have more than one EmploymentID i.e. hold more than two positions in the customer organization.
Since the age is represented on more than just one row per Employee we see this as a weighted average i.e. the total average sum will be weighted towards the employees with most employments i.e. most rows in the data.
Our example shows how the averages behave for two employees aged 30 and 60. The younger employee has two EmploymentIDs.
Average Age: Total[Age Sum]/Total[Rows] = 40
This is obviously wrong since the average age of the two employees of age 30 and 60 is 45, not 40.
The solution is to use DimAverage to adjust the average to the level we want in relation to the transaction level.
Age Average: Total[Age Sum]/Total[Rows]
True Average Age: DimAverage[EmployeeID,calc[Age Average]]
This will get the average for each EmployeeID represented in the total i.e. an unweighted total average. The summary will hence be correct when diving in the data.