POWER BI: Modeling

Average Functions

This demo illustrates different average functions you can write in Power BI. On how to summarize data quickly see summarizing data in Power BI.

Average functions

Example Formulas:

In the Power BI formula bar define the following measures:

                  avg_revenue = AVERAGE(TransactionData[Revenue])
                
	            

                  
                 avg_x_revenue = AVERAGEX(TransactionData,TransactionData[Revenue])
                   
	            

                  
                 avg_a_revenue = AVERAGEA(TransactionData[Revenue])
                   
	            

All of these functions return the same result in this instance. This is equivalent to taking the revenue column in your data and selecting average in the dropdown menu. However, only the first does exactly the same operations as described above.

Power BI Comparing Formulas Above:

AVERAGEX:

AVERAGEX Is useful when you need to perform row by row operations to evaluate an expression.

Example Formulas:

For example the following two measures are equivalent:

                    AverageDifference = AVERAGEX(TransactionData,TransactionData[Revenue] - TransactionData[Cost])
	            

                    Avg Profit per Transaction = DIVIDE([Profit], COUNT(TransactionData[Sales_Id]),0)
	            

Where Profit is a measure defined as:

                    Profit = SUM(TransactionData[Revenue]) - SUM(TransactionData[Cost])
	            

AVERAGEX vs DIVIDE and COUNT:

AVERAGEA:

AVERAGEA is used to handle non-numeric values. For instance, if one of the rows is not a number it will return 0 instead of an error. The way AVERAGEA behaves in POWER BI is currently different then in Excel and the DAX documentation. Below describes how AVERAGEA handles errors in Excel.

• For any row with a text (i.e string) or a blank value, returns 0.
• For Boolean Values return 1 for True, 0 for False.

EXAMPLE:

The following shows how AVERAGEA handles errors:

In both cases using the AVERAGEA function on both columns returns the same result. Average apples sold is 2, and average oranges sold is 2 likewise. That is because ? is a string which gets assigned 0, and TRUE is a boolean which is assigned a 1.