Washington State University



Featherman’s Analytics Adventures? – Charting the Window Functions MetricsRecall we had different types of calculated columns created with window functionsRunning totals – for example cummulative totals for the yearColumns created [Sub-Cat Total], [Sub-Cat Total by Year]Percent of total calculations – for example each month’s % of the year’s total Columns created [% of Sub-Cat], [% of Sub-Cat by Year], [% of All Bikes by Year], [% of All Bikes]Moving averages – for example the classic 3-month moving average.Columns created [Sub-Cat Moving Avg.], [Sub-Cat 3 Mo. Moving Avg.]Month over month (MOM) comparisons – for example comparing the level of units, revenue or profit for the current month to the prior. The same process is used for quarter over quarter comparisons, year over year comparisons, and rack over rack comparisons.a) numeric change - [Month over Month Revenue]b) percentage change - [MOM % Variance]c) creation of textual categories - [trend], [UpDown], [UpDown3]Creating groups such as quartiles – for example categorizing months of sales data into quartiles for example top 25%, bottom 25%Columns created [Sales Quartile], [Sales Quartile 2]Now lets create some charts that use these calculated columns. Turn on the PowerBI Desktop software, click get data | enter the server info and featherman_analytics as the database then click open the bok to paste in the SQL query in the appendix below. This is the same query as in the prior document, however the FORMAT() syntax has been stripped off so that all the columns remain numbers. While FORMAT() is great for making the data easier to read, when you use a professional dashboard software, often the data will not sort or present correctly because the FORMAT() command converts the numbers to text so that it can add the commas and % signs.Also as a teaser, we will take a look at some of PowerBI’s DAX date functions that are super easy to use (because they are written by and for busy Microsoft accountants and financiers). Advanced formulas introduced in a following epic adventure include CALCULATE(), PREVIOUSMONTH(), PREVIOUSQUARTER(), PREVIOUSYEAR(), SAMEPERIODLASTYEAR(), TOTALYTD(), and TOTALMTD(). Comparing business performance for different time periods must be very popular!! These save DAX formulas can also be written in Excel.AppendixUSE [Featherman_Analytics];SELECT [Sub Category], (YEAR(i.OrderDate) *100 ) + MONTH(i.OrderDate) AS [YearMonth], YEAR(i.OrderDate) as [Year], SUM(i.[OrderQuantity]) AS [Total Units], COUNT(DISTINCT([SalesOrderNumber])) as [# TA], SUM(i.[SalesAmount])AS [Monthly Revenue]--- Introducing the LAG function so that you can access a different row in the window frame , LAG(SUM(i.[SalesAmount]), 1, NULL)OVER (PARTITION BY [Sub category]ORDER BY (YEAR(i.OrderDate) *100 ) + MONTH(i.OrderDate)) AS [Prior Month] , SUM(i.[SalesAmount]) - LAG(SUM(i.[SalesAmount]), 1, NULL) OVER (PARTITION BY [Sub category] ORDER BY (YEAR(i.OrderDate) *100 ) + MONTH(i.OrderDate)) as [Month over Month Revenue]------ add two textual column metrics, IIF(SUM(i.[SalesAmount]) >LAG(SUM(i.[SalesAmount]), 1, NULL) OVER (PARTITION BY [Sub category] ORDER BY (YEAR(i.OrderDate) *100 ) + MONTH(i.OrderDate)) , 'Up', 'Down') as [UpDown], CASE WHEN SUM(i.[SalesAmount]) > 1.25 * (LAG(SUM(i.[SalesAmount]), 1, NULL) OVER (PARTITION BY [Sub category] ORDER BY (YEAR(i.OrderDate) *100 ) + MONTH(i.OrderDate))) THEN 'Up big' WHEN SUM(i.[SalesAmount]) > LAG(SUM(i.[SalesAmount]), 1, NULL) OVER (PARTITION BY [Sub category] ORDER BY (YEAR(i.OrderDate) *100 ) + MONTH(i.OrderDate)) THEN 'Up' WHEN (1.25 * SUM(i.[SalesAmount])) < LAG(SUM(i.[SalesAmount]), 1, NULL) OVER (PARTITION BY [Sub category] ORDER BY (YEAR(i.OrderDate) *100 ) + MONTH(i.OrderDate)) THEN 'Down big' WHEN SUM(i.[SalesAmount]) < LAG(SUM(i.[SalesAmount]), 1, NULL) OVER (PARTITION BY [Sub category] ORDER BY (YEAR(i.OrderDate) *100 ) + MONTH(i.OrderDate)) THEN 'down' END as [UpDown2] ---comparison to prior month revenue, SUM(i.[SalesAmount]) - LAG(SUM(i.[SalesAmount]), 1, NULL)OVER (PARTITION BY [Sub category]ORDER BY (YEAR(i.OrderDate) *100 ) + MONTH(i.OrderDate)) AS [Month Over Month] --comparison to prior month revenue = (new-old)/ old , (SUM(SalesAmount) - LAG(SUM(SalesAmount), 1, NULL) OVER (PARTITION BY ([Sub category]) ORDER BY [Sub category]) ) / LAG(SUM(SalesAmount), 1, NULL) OVER (PARTITION BY ([Sub category]) ORDER BY [Sub category]) AS [MOM % Variance] -- this is a running total that resets for each SubCategory , AVG(SUM(i.[SalesAmount])) OVER(PARTITION BY [Sub Category]ORDER BY (YEAR(i.OrderDate) *100 ) + MONTH(i.OrderDate)ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) AS [Sub-Cat Moving Avg.] , AVG(SUM(i.[SalesAmount])) OVER(PARTITION BY [Sub Category]ORDER BY (YEAR(i.OrderDate) *100 ) + MONTH(i.OrderDate)ROWS BETWEEN 2 PRECEDING and CURRENT ROW) AS [Sub-Cat 3 Mo. Moving Avg.]-- this is a running total that resets for each SubCategory and Year,SUM(SUM(i.[SalesAmount])) OVER(PARTITION BY [Sub Category], YEAR(i.OrderDate)ORDER BY (YEAR(i.OrderDate) *100 ) + MONTH(i.OrderDate)) AS [Sub-Cat Total by Year] -- this is a running total that resets for each SubCategory ,SUM(SUM(i.[SalesAmount])) OVER(PARTITION BY [Sub Category]ORDER BY (YEAR(i.OrderDate) *100 ) + MONTH(i.OrderDate)) AS [Sub-Cat Total]-- here records are categorized into quartiles. , NTILE(4) OVER (PARTITION BY [Sub Category], YEAR(i.OrderDate)ORDER BY SUM(i.[SalesAmount]) DESC )AS [Sales Quartile], CASE NTILE(4) OVER (PARTITION BY [Sub Category], YEAR(i.OrderDate)ORDER BY SUM(i.[SalesAmount])DESC) WHEN 1 THEN 'Gangbusters'WHEN 2 THEN 'Great month'WHEN 3 THEN 'Break-even month'WHEN 4 THEN 'Sub-par month'END AS [Sales Quartile 2]-- All these columns of analytics need to matchup with the GROUP BY fields.FROM [dbo].[FactInternetSales] as iINNER JOIN [dbo].[AW_Products_Flattened] as p ON p.[Productkey] = i.[Productkey]WHERE [Category] = 'Bikes'GROUP BY [Sub Category], YEAR(i.OrderDate), (YEAR(i.OrderDate) *100 ) + MONTH(i.OrderDate)ORDER BY [Sub Category], [YEAR], [YearMonth] ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download