QUARTILE.EXC
In my recent quest to create or catalog as many DAX equivalents for Excel functions, this just adds to the struggles with Excel's QUARTILE function. So, in numerous locations, such as this; not that...God forbid, Quora is authoritative about anything, but in mulitiple places there are two documented methods of calculating 1st and 3rd quartiles and they all seem to essentially agree about the general nature of those two methods. So I implemented the exclusive method here but the results seem to come out to more along the lines of the inclusive method, not that I trust Excel's calculations of those either. So, who knows at this point. Here it is though. QUARTILE.EXC = VAR __Values = SELECTCOLUMNS('Table',"Values",[Column1]) VAR __Quart = MAX('Quartiles'[Quart]) VAR __Median = MEDIANX(__Values,[Values]) VAR __Count = COUNTROWS(__Values) VAR __Quartile = SWITCH(__Quart, 0,MINX(__Values,[Values]), 2,__Median, 4,MAXX(__Values,[Values]), 1, VAR __Median = IF( ISEVEN(__Count), MEDIANX(FILTER(__Values,[Values] < __Median),[Values]), MEDIANX(FILTER(__Values,[Values] <= __Median),[Values]) ) RETURN __Median, 3, VAR __Median = IF( ISEVEN(__Count), MEDIANX(FILTER(__Values,[Values] > __Median),[Values]), MEDIANX(FILTER(__Values,[Values] >= __Median),[Values]) ) RETURN __Median ) RETURN __Quartile All I can say is that apparently either everyone else in the entire world (as far as I can find) is wrong about how to calculate quartiles or...maybe I am missing something. Something else that bugs me, all of the documentation on QUARTILE.INC, QUARTILE.EXC, PERCENTILE.INC, PERCENTILE.EXC all focus on the "inclusive/exclusive" part about the kth values from 0..1. Except that seems like the least important part to me because there are clearly different methods going on here in terms of how these functions compute the quartiles/percentiles because you can get very different answers, especially when dealing with even numbers of items. The fact that you can't use 0 and 1 in one of them seems like the last thing that you would want to explain but rather explain why the calculated values are different? And another thing with regard to the "interpolation", apparently that is why the numbers generated for the 1st and 3rd quartiles in Excel varies from the way everybody else does it so how exactly is this interpolation happening and why is it better or worse than the way everyone else seems to do it? eyJrIjoiNzQxZTc1ZDgtMmE2Ni00NDE0LWExNjktZWJiMzBhZTk3Y2UyIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Measure Totals, The Final Word
With apologies to Theodor Geisel... Measure totals have you perturbed? Fear not! It's Measure Totals, The Final Word, These measures work with matrices, They work with tables, They work with rows and columns and labels. They work in the daytime, They work at night, They work to make sure the totals are right! Now that you've seen them, Now that you've heard, Shout it out loud, it's Measure Totals, The Final Word! At some point, we've all been frustrated by measure totals. If you want to understand why, read this post. The technique employed here is fairly simple and should work in all "standard" cases of where you just want the Total line to, well, display the total (sum) of a measure. For more complex scenarios, see my Matrix Measure Total Triple Threat Rock & Roll measure. Essentially, create a measure, any measure, that performs your desired calculation and returns the correct result at the row level. This becomes your "m_Single" measure. Now, create an "m_Total" measure that performs a SUMMARIZE of your data, exactly as how it is displayed in your table or matrix and use the "m_Single" measure within that SUMMARIZE statement to provide the values for the individually summarized rows. Finally, perform a SUMX across that summarized table. The measures presented in this PBIX file also do a HASONEVALUE check that isn't really necessary in most cases but perhaps lends a little confidence to the user that the SUMX is only employed in the Total line and might also add some performance improvements. In effect, you are recreating the displayed visualization in memory as a table and then doing a summation across that table for the total line, as you would intuitively expect a total line in a table or matrix to work. So, if we have a measure like: m_Single = SUM(Table1[Value])-50 This measure will cause problems in total lines. So, if we are summarizing by [Name], we create this measure: m_Total 1 = VAR __table = SUMMARIZE('Table1',[Name],"__value",[m_Single]) RETURN IF(HASONEVALUE(Table1[Name]),[m_Single],SUMX(__table,[__value])) If we are summarizing by [Category1], we create this measure: m_Total 2 = VAR __table = SUMMARIZE('Table1',[Category1],"__value",[m_Single]) RETURN IF(HASONEVALUE(Table1[Category1]),[m_Single],SUMX(__table,[__value])) And so on... We use these "m_Total" measures in our visualizations. The "m_Single" measure is still used, but not directly in the visuals themselves. Is it annoying to have to create multiple measures and specifically tailor them to each individual visual? Yes, yes it is. eyJrIjoiODBmNmI4YjItZTMwYi00ZDU4LTg0MWItMzYyZWU3ODk4ZWI4IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9BINOM.INV Approximation
Been a while since I published a Quick Measure. Here is one that is an update to my previously published BINOM.INV. The problem with that version is that it relied on the FACT function which tops out at 170. So, for populations larger than 170 use this one which is an extremely accurate "approximation". BINOM_INV_APPROX = VAR __n = [n Value] VAR __Alpha = [Alpha Value] VAR __p = [p Value] VAR __pi = PI() -- Stirling's approximation for ln(n!) defined inline VAR __LnFactorial = ADDCOLUMNS( GENERATESERIES( 0, __n ), "ln_n!", VAR __k = [Value] VAR __Result = IF( __k = 0, 0, __k * LN( __k ) - __k + 0.5 * LN( 2 * __pi * __k ) ) RETURN __Result ) -- Join ln(n!), ln(k!), ln(n-k!) for each k VAR __Table = ADDCOLUMNS( __LnFactorial, "ln_k!", MAXX( FILTER( __LnFactorial, [Value] = EARLIER( [Value] ) ), [ln_n!] ), "ln_n-k!", MAXX( FILTER( __LnFactorial, [Value] = __n - EARLIER( [Value] ) ), [ln_n!] ), "LogB", VAR __k = [Value] VAR __ln_n_fact = __n * LN(__n) - __n + 0.5 * LN(2 * __pi * __n) VAR __ln_k_fact = IF( __k = 0, 0, __k * LN( __k ) - __k + 0.5 * LN( 2 * __pi * __k ) ) VAR __ln_nk_fact = VAR __nk = __n - __k VAR __Result = IF( __nk = 0, 0, __nk * LN( __nk ) - __nk + 0.5 * LN( 2 * __pi * __nk ) ) RETURN __Result VAR __Result = __ln_n_fact - __ln_k_fact - __ln_nk_fact + __k * LN( __p ) + ( __n - __k ) * LN( 1 - __p ) RETURN __Result ) VAR __TableWithProb = ADDCOLUMNS( __Table, "B", EXP( [LogB] ) ) VAR __CumulativeTable = ADDCOLUMNS( __TableWithProb, "Cumulative", SUMX( FILTER( __TableWithProb, [Value] <= EARLIER( [Value] ) ), [B] ) ) VAR __Result = MINX( FILTER(__CumulativeTable, [Cumulative] >= __Alpha), [Value] ) RETURN __Result eyJrIjoiMDMxMzM1ZDktZDdlYy00NWFiLWE1MWMtYzU1ZTU3ZmZkOGYzIiwidCI6Ijg3NDlmOWI5LWYzMmQtNDdhMS1hMjI0LTM2OTQxOGFlMmY1MSJ9Days of Supply
Suppose you have a weekly forecast of inventory and demand and you wish to know for each week the number of days of supply that you have on hand. That is the purpose of this Quick Measure. Inputs are the current week and inventory as well as the demand column. Days of Supply = // Get the current week and inventory for the current row VAR __week = MAX([Week]) VAR __inventory = MAX([Ending on hand Inventory]) // Create a table of all weeks greater than the current week VAR __table = FILTER(ALL(Inventory),[Week]>__week) // Add our current inventory from above to each row VAR __table1 = ADDCOLUMNS(__table,"__start",__inventory) // Add a running total of demand to each row VAR __table2 = ADDCOLUMNS(__table1,"__demand",SUMX(FILTER(__table1,[Week]<=EARLIER([Week])),[Demand])) // Add the difference in start versus the running total of demand to each row VAR __table3 = ADDCOLUMNS(__table2,"__left",[__start] - [__demand]) // Create a table that only has the positive rows VAR __table4 = FILTER(__table3,[__left]>=0) // With only the positive rows, the MIN is the last row before demand runs out VAR __min = MINX(__table4,[__left]) // Therefore, our base days is the number of rows in this table * 7 VAR __baseDays = COUNTROWS(__table4)*7 // Grab the MAX value of the negative rows, this is the row right after our inventory runs out VAR __max = MAXX(FILTER(__table3,[__left]<0),[__left]) // Divide the row right before the invetory ran out by the sum of the absolute values of right before and after // the inventory ran out. This is the percentage of days in that week before inventory ran out. multiply this by 7 // and this is the number of days in that week before inventory ran out VAR __extraDays = __min / (__min + ABS(__max)) * 7 RETURN __baseDays + __extraDays Interestingly, this Quick Measure exhibits a form of "looping" in DAX, or at least a work-a-round. Consider that a primary task of this measure is to determin the week in which inventory "runs out". In traditional programming, one would determine this with something like a for or while loop, checking for a boundary condition of the inventory on hand becoming negative with respect to demand. However, in DAX, there are no for or while looping constructs. Thus, instead we create a temporary table where each row in the table represents one pass or iteration through a traditional programming "loop". We can then use our boundary condition to filter down to the specific rows where that boundary condition occurs in order to perform our calculation. As demonstrated in the DAX code above, we can determine the values on either side of our boundary condition as well as how many "interations" were required in order to hit that boundary condition. eyJrIjoiZDcxY2U3ZjAtM2ZiMy00ZjJhLWE0N2YtZTM5YjFiNDJlMTJlIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Periodic Billing
Again, thanks to @Phil_Seamark's insightful guidance and examples in his fantastic new book, Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence, I finally "get" the GENERATE function and how it can be used to elegantly solve problems that have vexed me since almost the very first Power BI model that I ever built, dealing with data that contains date ranges. This one uses the same technique as Open Tickets but puts a different spin on it by also requiring that there be a periodic element to the totals calculation. The following measure assumes a disconnected date table and data that involves billing starting and ending dates with a monthly fee. The measure computes the total revenue within any particular month, which can then be plotted. Also nifty. Total Amount = VAR tmpCalendar = ADDCOLUMNS('Calendar',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#"))) VAR tmpBilling = ADDCOLUMNS('Billing',"MonthYearBegin",VALUE(YEAR([BeginDate]) & FORMAT(MONTH([BeginDate]),"0#")), "MonthYearEnd",VALUE(YEAR([UntilDate]) & FORMAT(MONTH([UntilDate]),"0#"))) VAR tmpTable = SELECTCOLUMNS( FILTER( GENERATE( tmpBilling, SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear]) ), [MonthYear] >= [MonthYearBegin] && [MonthYear] <= [MonthYearEnd] ), "Customer",[Customer], "Year",[Year], "Month",[Month], "Amount",[Amount] ) RETURN SUMX(tmpTable,[Amount]) Again, if you are only going to own one DAX book, IMHO, Phil's is the book you want! eyJrIjoiN2IyMGNlYmItZjhjNi00M2IxLWI1MDAtZmVkMzIxMjkzNmFhIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Better Rolling Average
Continuing with exploring alternatives to Power BI's default quick measures that don't involve the CALCULATE function, such as Better Running Total, Better Average per Category, Better Weighted Average per Category, Better Filtered Value, Better Sales from New Customers, and Year to Date Total and Year Over Year Change, this one tackles Rolling Average. Power BI's Rolling Average quick measure returns something like this: Value rolling average = IF( ISFILTERED('Dates'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __LAST_DATE = ENDOFMONTH('Dates'[Date].[Date]) VAR __DATE_PERIOD = DATESBETWEEN( 'Dates'[Date].[Date], STARTOFMONTH(DATEADD(__LAST_DATE, -3, MONTH)), __LAST_DATE ) RETURN AVERAGEX( CALCULATETABLE( SUMMARIZE( VALUES('Dates'), 'Dates'[Date].[Year], 'Dates'[Date].[QuarterNo], 'Dates'[Date].[Quarter], 'Dates'[Date].[MonthNo], 'Dates'[Date].[Month] ), __DATE_PERIOD ), CALCULATE(SUM('Table'[Value]), ALL('Dates'[Date].[Day])) ) ) Perhaps a better way: Better Rolling Average = VAR __EndDate = MAX('Table'[Date]) VAR __3MonthsAgo = EOMONTH(__EndDate, -3) VAR __StartDate = DATE(YEAR(__3MonthsAgo), MONTH(__3MonthsAgo), 1) VAR __Table = SUMMARIZE( FILTER(ALL('Table'),[Date]>=__StartDate && [Date]<=__EndDate), 'Table'[Month], "__Value",SUM('Table'[Value]) ) RETURN AVERAGEX(__Table,[__Value]) And the video: eyJrIjoiZjc5MDlhYjktOWYzZi00YzM3LWFlYWEtYWMyMGQyNzM4NGYwIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Power BI: Show weekend Data on Monday or Friday
Problem: I have data for all weekdays, but I do not want to show data on weekends, I would like to show the same on Monday Solution: There is a solution like creating a new date column and moving weekend to Monday or Friday and join the new column with the date. But we would like to have a solution using only measures. This means we need to move weekend data by 1–2 days on either side Columns I already have in the Model Discount = [Gross Sales]*[Dis Per]/100.0 Gross Sales = [Qty] * [Price] Net Sales = [Gross Sales] - [Discount] Measure I am using Net = Sum(Sales[Net Sales]) New measures created for weekend data moved to Monday Net non Work = CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) >=6 )) Net non work 1 = CALCULATE(CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) =6 )) , dateadd('Date'[Date],-2,DAY)) + CALCULATE(CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) =7 )) , dateadd('Date'[Date],-1,DAY)) Net Work = CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) <6 )) Net Show on Monday = [Net Work] + [Net non work 1] New measures created for Friday Net non work b 1 = CALCULATE(CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) =6 )) , dateadd('Date'[Date],1,DAY)) + CALCULATE(CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) =7 )) , dateadd('Date'[Date],2,DAY)) Net Show on Friday = [Net Work] + [Net non work b 1] Find more details on the BlogDynamic Segmentation/ Bucketing/ Binning
Dynamic Segmentation/ Bucketing/ Binning Created an independent bucket Table. Create measures that take advantage of those buckets Actual Measure Margin % = DIVIDE([Margin],[Sales]) The measure we would like to become the parameter/slicer Margin Type = Switch( True(), [Margin %] < -.2 , "Very Bad", [Margin %] <0 , "Bad", [Margin %] <.1 , "Netural", [Margin %] <.25 , "Good", "Very Good" ) The independent Table we created Start Limit End Limit Bucket -1000 -0.2 Very Bad -0.2 0 Bad 0 0.1 Netrual 0.1 0.25 Good 0.25 1000 Very Good Bucketed measures Margin Bucket = COUNTX(filter(VALUES(customer[Customer Id]),[Margin %] >=Min('Margin Bucket'[Start Limit]) && [Margin %] <max('Margin Bucket'[End Limit])),customer[Customer Id]) Avg Margin Bucket = AVERAGEX(filter(VALUES(customer[Customer Id]),[Margin %] >=Min('Margin Bucket'[Start Limit]) && [Margin %] <max('Margin Bucket'[End Limit])),[Margin %]) eyJrIjoiYmFmMTc0NzYtYzMzNS00NTU0LWFjNGYtODc4ZjA0ODM0MzVjIiwidCI6ImVhOGJkMWZkLWFjMzQtNGFlMi1iNDIxLTZjZmEyZmNmZjI0MyJ9ATAN2
DAX doesn't have an ATAN2 function so I created one. ATAN2 = VAR __x = MAX('Table'[X]) VAR __y = MAX('Table'[Y]) VAR __atan2 = SWITCH( TRUE(), __x > 0, ATAN(__y/__x), __x < 0 && __y >= 0, ATAN(__y/__x) + PI(), __x < 0 && __y < 0, ATAN(__y/__x) - PI(), __x = 0 && __y > 0, PI()/2, __x = 0 && __y < 0, PI()/2 * (0-1), BLANK() ) RETURN __atan2 eyJrIjoiNWYyNmRlYzctY2Q1NC00NDQ0LTlkYWQtOTVhNjljMTMzN2RjIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9TRIMMEAN
When you think you pick an easy one... In my recent quest to create or catalog as many DAX equivalents for Excel functions, I figured this one would be a cinch. Well, not so much. Between poor documentation and vexing issues with DAX not having any kind of inherent sort for data, I very nearly pulled my hair out over this one and at times felt very much like the young woman in the photo. Well, anyway, a double, concurrent while loop and several burnt out, overloaded brain cells later, I was apparently able to solve a 2 1/2 year old request and I guess I was correct back then, it would require RANKX but that was only just the beginning! So cspress , here is your TRIMMEAN. Apologies for the delay... TRIMMEAN = VAR __Table = ADDCOLUMNS( 'Table', "Rank",RANKX('Table',[Value]) ) VAR __Percent = .2 VAR __Count = COUNTROWS(__Table) VAR __Trim = MROUND(__Count * __Percent,2) / 2 VAR __MaxRank = MAXX(__Table,[Rank]) VAR __MinRank = MINX(__Table,[Rank]) VAR __RanksTable = ADDCOLUMNS( ADDCOLUMNS( GROUPBY( __Table, [Rank], "Count",COUNTX(CURRENTGROUP(),[Value]), "Value",MAXX(CURRENTGROUP(),[Value]) ), "CumulativeBottomCount",COUNTROWS(FILTER(__Table,[Rank] >= EARLIER([Rank]))), "CumulativeTopCount",COUNTROWS(FILTER(__Table,[Rank] <= EARLIER([Rank]))) ), "BottomWhile",__Trim - [CumulativeBottomCount], "TopWhile",__Trim - [CumulativeTopCount] ) VAR __MinBottom = MAXX(FILTER(__RanksTable,[BottomWhile]<=0),[BottomWhile]) VAR __MinTop = MAXX(FILTER(__RanksTable,[TopWhile]<=0),[TopWhile]) VAR __FinalBottomRankTable = ADDCOLUMNS( FILTER(__RanksTable,[BottomWhile]>=__MinBottom), "Product",IF([BottomWhile]>=0,[Count]*[Value],([Count] + [BottomWhile]) * [Value]) ) VAR __FinalTopRankTable = ADDCOLUMNS( FILTER(__RanksTable,[TopWhile]>=__MinTop), "Product",IF([TopWhile]>=0,[Count]*[Value],([Count] + [TopWhile]) * [Value]) ) VAR __Bottom = SUMX(__FinalBottomRankTable,[Product]) VAR __Top = SUMX(__FinalTopRankTable,[Product]) RETURN DIVIDE( SUMX(__Table,[Value]) - __Bottom - __Top, __Count - 2 * __Trim ) To clarify what is going on here, TRIMMEAN in Excel essentially ranks your data and trims off a number of rows equal to the percentage specified. The documentation doesn't really tell you about the ranking part, but it does it, it is not just trimming off the ordered list of rows. So, per the documentation, TRIMMEAN rounds the number of excluded data points down to the nearest multiple of 2. If percent = 0.1, 10 percent of 30 data points equals 3 points. For symmetry, TRIMMEAN excludes a single value from the top and bottom of the data set. Great. Where you run into trouble is when you have ties at the top and bottom of your dataset. Excel's TRIMMEAN is smart enough to only trim off the correct number of rows. So if you are trimming 3 points off the top and the bottom and have 2 1's and 3 2's, Excel's TRIMMEAN will only trim off the 2 1's and a single 2. Emulating this in DAX is not straight-forward at all and requires a lot of table gymnastics, double concurrent while loops and so on, such as me lying down on my bed with my eyes shut trying to figure out how to solve this problem until I got enough of an idea to drag myself back to my computer and continuing working on it. If you are wondering, the idea was around taking the averages of the tops and bottoms and multiplying that by the number of items to trim off from each side. Didn't actually work at all, but it eventually led me to the above solution, which I *think* works for all cases. eyJrIjoiMmUyZjEzNDgtMWNhNC00OGI0LWE2ZDktNjA2ZmY1ZGVkMDdiIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
