DAX and Average
Summary
When you start to get more active within Power BI and especially as you start to deploy larger datasets reporting on manual systems you must prepare for errors, there is nothing inherently wrong with getting errors, but they must be managed. A sign of a good developer is to be able to assess errors and make changes to their model accordingly. In the example, we look at an error that has arisen due to a misunderstanding – the expectation was that a column would be numbers, but instead, there are some text values. While investigating a solution is offered, but there is a better one, comment below if you can think of it.
In Power BI, statistical functions are vital. Many are just included within DAX, although others require some work. This short video covers the three averages (Mean, Median and Mode) as well as dipping into using Standard Deviation as well to apply some context to your Average. As you progress in your Power BI development, you’ll find you start to rely on these column functions. You should also note that measures can reference other measures as we do to get the Standard Deviation to appear on our charts.
EXAMPLES
Mean = AVERAGE ( [Column] )
Median = MEDIAN ( [Column] )
Mode =MINX (
TOPN (
1,
ADDCOLUMNS (
VALUES ( [Column] ),
“Frequency”, CALCULATE ( COUNTROWS( Table ))
),
[Frequency],
0
),
[Column]
)
From Dataset 006 Loans
Total Loans = SUM ( Offers[(case)_RequestedAmount] )
Average Loan = AVERAGE ( Offers[(case)_RequestedAmount] )
Median Loan Amount = MEDIAN ( Offers[(case)_RequestedAmount] )
Standard Deviation = STDEV.P ( Offers[(case)_RequestedAmount] )
Lower Bound = [Average Loan] – [Standard Deviation]
Upper Bound = [Average Loan] + [Standard Deviation]
Modal Average =
VAR TempTable = SUMMARIZE( Offers, Offers[(case)_RequestedAmount],”Frequency”, COUNTROWS(Offers))
RETURN
// Use an aggregation function because we have to return a single value (MINX, MAXX recommended)
MAXX (
//Return the TOP row ONLY of the temporary table based on the frequency or number of items sorted descending
TOPN (
1,
TempTable,
[Frequency],
0
),
Offers[(case)_RequestedAmount]
)