This section covers some basic stats functions and formulas you can apply to your Power BI analysis:
For a full list of statistical functions
A Daily Average is the average value for all dates in a given date range. While you might be able to get away with summing up the totals and dividing by the number of days in the month, a more robust way that works for any date range you filter, whether that by year, quarter or month is shown below:
Example: Find Daily Average Revenue
Daily Avg = AVERAGEX(CalenderDate,CALCULATE(SUM(TransactionData[Revenue])))
Remember: Use a unique and seperate caldendar table for date values.
Example: Find the smallest sales for any date range.
Largest Value = MINX(CalenderDate,CALCULATE(SUM(TransactionData[Revenue])))
Example: Find the largest sales for any date range.
Largest Value = MAXX(CalenderDate,CALCULATE(SUM(TransactionData[Revenue])))
Ideas for using MINX or MAXX
Variance is a measure of dispersion of your data from its average value. The variance states how much your data fluctuates or varies. Standard Deviation is another measure for dispersion. It is the square root of variance, and will have the same units as the average value. When used togther with the mean you can find a range of expected values.
STDEV of Sales = STDEVX.S(CalenderDate,CALCULATE(SUM(TransactionData[Revenue])))
The following gives the standard deviation for your daily sales. Learn more about Standard Deviation.
Try It OutUse RANKX to rank items from from largest to smallest based. For instance in POWER BI we write:
Rank Sales_By_State = RANKX(ALL(states[state]),CALCULATE(SUM(TransactionData[Revenue])))
Try It Out
This ranks states in order based off of total revenue. Notice the ALL function wraps our column states[state] in the first parameter. When using the RANKX function you need to use ALL around your table or column names.
All of the statistical measures above have to same format. They have a Calendar Date table as the first parameter, and a expression to evaluate as the second argument.
If you need to evaluate a specific column rather than a table, use the ALL function to wrap that column in. Since a table is needed in the first parameter, the ALL function essentially converts the column into a "temporary table" which can then be used.
For the second parameter, if you are not using a specific measure then you need to wrap CALCULATE around the metric that is aggregrated (as shown above). This is needed to perform a context transition.