Power Pivot Principles: The A to Z of DAX Functions – GEOMEANX

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at GEOMEANX. 

The GEOMEANX function

In mathematics, the geometric mean is a mean or average, which indicates the central tendency or typical value of a set of numbers by using the product of their values (as opposed to the arithmetic mean which uses their sum).  The geometric mean is defined as the nth root of the product of nnumbers, i.e. for a set of numbers x₁, x₂, ..., x_n, the geometric mean is defined as 

In two dimensions, it is the equivalent of finding the equivalent square with the same area as the rectangle given by the two dimensions cited:

In three dimensions, it is the equivalent of finding the equivalent cube with the same volume as the given hexahedron with the three dimensions cited:

The idea continues in n dimensions.

The GEOMEANX function is one of statistical functions and, it returns geometric mean of an expression values in a table.  It employs the following syntax:

It has two [2] arguments:

• table: this is required and represents the table containing the rows for which the expression will be evaluated
• expression: this is also required and represents a field (column), or an expression that evaluates to a field, to be evaluated for each row of the table.

The following should be noted:

• the expression argument of GEOMEANX function only counts numerical values.  Blanks, logical values and text are ignored
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Let’s consider the following example we have tbl_Data table loaded into the Data Model:

We can write the following measure to calculate the GEOMEANX:

Then we go to Insert -> PivotTable -> From Data Model and we drag Name to the Rows and GEOMEANX to the Value fields here:

This will display the geometric mean for each Name here.

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.