POWER BI: Modeling

Statistical Functions in Power BI

This section covers some basic stats functions and formulas you can apply to your Power BI analysis:

  • Using AVERAGEX to find the daily average
  • MIN and MAX functions to find extreme values
  • Standard Deviation to see how your data varies
  • RANK functions to see how value stack up against each other

For a full list of statistical functions

DAILY AVERAGE

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])))
	            
  • AVERAGEX calculates the average for an expression that is evaluated over a table.
  • The expression is the value that we would like to return, which we must wrap inside the CALCULATE function.

Remember: Use a unique and seperate caldendar table for date values.


MINX

Example: Find the smallest sales for any date range.

                      Largest Value = MINX(CalenderDate,CALCULATE(SUM(TransactionData[Revenue])))
	                

MAXX

Example: Find the largest sales for any date range.

                      Largest Value = MAXX(CalenderDate,CALCULATE(SUM(TransactionData[Revenue])))
	                

Ideas for using MINX or MAXX

  • Finding the smallest transaction or order amount
  • Finding the lowest value customer
  • Finding region with lowest ROI
  • Customers/businesses with highest AOV
  • Customers/businesses with the highest profit margin
  • Highest Churn Rate by product
Variance and Standard Deviation

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 Out
RANKX

Use 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.

SUMMARY

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.


Dash-Intel is a Power BI and Tableau resource site for data visualization and building BI dashboards.

Data Analyst & Consultant

Copyright 2015-2023 Dash-Intel.com Terms