Measure 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. eyJrIjoiODBmNmI4YjItZTMwYi00ZDU4LTg0MWItMzYyZWU3ODk4ZWI4IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Ticket Backlog (How many were open on specific dates)
Incident Backlog = VAR CurrentDate = MAX('Date'[Date]) VAR ActiveTickets = CALCULATE(DISTINCTCOUNT(Incidents[Number]), ALL('Date'), 'Date'[Date]<=CurrentDate, ISBLANK(Incidents[Resolved Date]) ||Incidents[Resolved Date]>=CurrentDate) Return ActiveTicketsDynamic 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 %]) eyJrIjoiYmFmMTc0NzYtYzMzNS00NTU0LWFjNGYtODc4ZjA0ODM0MzVjIiwidCI6ImVhOGJkMWZkLWFjMzQtNGFlMi1iNDIxLTZjZmEyZmNmZjI0MyJ9Chelsie Eiden's Duration
Chelsie Eiden is my new favorite human being on the face of the planet. I don't know her major but, even if she is majoring in math, it still wouldn't change my mind on this one. That's how much I like this individual. The reason she is my favorite human being on the face of the planet is because she has finally...FINALLY, solved a "problem" with Power BI that is, ohhhh, say at least 4 or 5 years old. Since the dawn of Power BI there has been this problem with aggregating duration in HH : MM : SS format. You could convert it to seconds to aggregate it but you couldn't display it in the hours, minutes, seconds format in a visual that properly aggregated it in column charts because the minute you did a concatenation or a format on it, "POOF" it became text. Maddening!! I have been harping on this issue for, well, forever, such as in this post I did with konstantinos ages ago. So, Chelsie, thank-you, thank-you, thank-you from the bottom of my heart! I have named this new Quick Measure just for you. Chelsie Eiden's Duration = // Duration formatting // * @konstatinos 1/25/2016 // * Given a number of seconds, returns a format of "hh:mm:ss" // // We start with a duration in number of seconds VAR Duration = SUM([Duration]) // There are 3,600 seconds in an hour VAR Hours = INT ( Duration / 3600) // There are 60 seconds in a minute VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60) // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number RETURN // We put the hours, minutes and seconds into the proper "place" Hours * 10000 + Minutes * 100 + Seconds All but the last line is the code from that article that konstantinos and I wrote years and years ago. The only difference is the last line. Once you have this measure, then all you have to do is implement Chelsie Eiden's Custom Format String with a value of "00:00:00" (no double quotes). Boom!! https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-september-2019-feature-summary/#customFormatStrings eyJrIjoiYjE5ZDZkN2EtODdlNy00ZmUxLWIyOGItOWRhYjU0NDY2Y2VhIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9TRIMMEAN
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. eyJrIjoiMmUyZjEzNDgtMWNhNC00OGI0LWE2ZDktNjA2ZmY1ZGVkMDdiIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Matrix Measure Total Triple Threat Rock & Roll
While it would be a bit presumptuous to presume that any single formula could account for all possible measure total situations, the following pattern is presented for handling matrix measure values as well as subtotals and grand totals. This pattern has the added flexibility of being able to handle the same or different aggregation calculations at all three levels. In the example provided, the "normal" aggregation is MIN while at the the subtotal level it is the AVERAGE of those MIN values. At the grand total level, it is the MAX of the AVERAGE of the subtotals. Overall, this pattern provides extreme flexibility and can be extended to any number of level subtotals. The main assumption is that there is a "normal aggregation" measure that one is wishing to display in a matrix with correct subtotals and grand total. In this example, the formula for this normal aggregation is "Normal Aggregation = MIN('Table'[Occupancy %]). MM3TR&R = VAR __Category1 = MAX([Category1]) VAR __tmpTable = SUMMARIZE( ALLSELECTED('Table'), 'Table'[Category1], 'Table'[Category2], "Aggregation",[Normal Aggregation] ) VAR __SubTotal = AVERAGEX( FILTER( __tmpTable, 'Table'[Category1]=__Category1 ), [Aggregation] ) VAR __GrandTotal = MAXX( GROUPBY( __tmpTable, [Category1], "GTAggregation", AVERAGEX(CURRENTGROUP(),[Aggregation]) ), [GTAggregation] ) RETURN IF( HASONEVALUE('Table'[Category1]) && HASONEVALUE('Table'[Category2]), [Normal Aggregation], IF(HASONEVALUE('Table'[Category1]), __SubTotal, __GrandTotal ) ) eyJrIjoiNzMyYTYwY2QtMGVlMC00MjdmLWIwNDUtYmQ1ZDFjNWZiM2E5IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Dashboard Template
A Power BI template featuring a heatmap as a calendar-formatted matrix and buttons that reference bookmarks for day, week, month, quarter, and year to adjust the date timeline selection and the X-axis date hierarchy on trend charts. It includes date filtering options based on timeframes and ageing. This template was developed for on-premises Power BI Report Server but also works in the Service. The test data was created by using https://mockaroo.com/ .Hour Breakdown
This Quick Measure breaks a start and end time down into the number of minutes for each hour of the day. There are actually two measures included in order to demonstrate how to use the techniques in Measure Totals, The Final Word to display the correct totals and subtotals within a matrix. Hour Breakdown = VAR __currentHour = HOUR(MAX('Hours'[Hour])) VAR __startHour = HOUR(MIN('Data'[Start])) VAR __endHour = HOUR(MAX('Data'[End])) VAR __table = GENERATESERIES(__startHour,__endHour,1) VAR __table1 = ADDCOLUMNS(__table,"__minutes", SWITCH(TRUE(), __startHour < __endHour && [Value] <> __endHour && [Value] <> __startHour,60, __startHour < __endHour && [Value] = __endHour,MINUTE(MAX('Data'[End])), 60-MINUTE(MAX(Data[Start])) ) ) VAR __table2 = FILTER(__table1,[__minutes]>0) RETURN SUMX(FILTER(__table2,[Value] = __currentHour),[__minutes]) Hour Breakdown Total = VAR __table = SUMMARIZE('Data',[Date],[ID]) VAR __table1 = GENERATE(__table,Hours) VAR __table2 = ADDCOLUMNS(__table1,"__duration",[Hour Breakdown]) RETURN IF(HASONEVALUE(Hours[Hour]) && HASONEVALUE(Data[ID]),[Hour Breakdown],SUMX(__table2,[__duration])) eyJrIjoiZjYxYjUzYTEtMTM5ZS00NjAwLWEyZjgtNmM4MmFjMzEyODBhIiwidCI6Ijg3NDlmOWI5LWYzMmQtNDdhMS1hMjI0LTM2OTQxOGFlMmY1MSJ9Better Running Total
Microsoft's running total quick measure, well, it's just not very good. It's overly complex and doesn't work in single table situations. There's a better way as shown in this video: MSHGQM - Don't Use CALCULATE! - YouTube For reference, Microsoft's running total quick measure generates code such as: Value running total in Date = CALCULATE( SUM('Table'[Value]), FILTER( ALLSELECTED('Table'[Date]), ISONORAFTER('Table'[Date], MAX('Table'[Date]), DESC) ) ) As shown, this running total doesn't work in single table situations. A better, less complex way to create a running total that works with a single table is like this: Better RT = VAR __Date = MAX('Table'[Date]) VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date) RETURN SUMX(__Table,[Value]) And with just a minor change, this method also works if you have a separate Dates table: Better RT 2 = VAR __Date = MAX('Dates'[Date]) VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date) RETURN SUMX(__Table,[Value]) Watch the video! eyJrIjoiMTcxZWJiZmEtZDdiMy00YWYyLWEyOTYtMmI1MDQ4YjlmMTY5IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Increased, Decreased, No Change
Came out of this post here: Re: Looking for help with measures please - Microsoft Fabric Community Basically, given a set of date-series data, how many customers have increased their score, descreased their score, or remained unchanged from their first data point to their last data point. Also calculates the percentage that have increased, decreased, or remained the same as well as the average number of data points for customers that have increased, decreased, or remained the same. The measures below are for increases, the other measures are nearly identical and are in the attached PBIX file. Updated to include a variation that excludes customers with only 1 appointment. Total Icreased Measure = VAR __Table = ADDCOLUMNS( ADDCOLUMNS( DISTINCT( 'Table'[Customer ID] ), "__FirstDateScore", VAR __CustomerID = [Customer ID] VAR __FirstDate = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] ) VAR __FirstScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __FirstDate ), [Score] ) RETURN __FirstScore, "__LastDateScore", VAR __CustomerID = [Customer ID] VAR __LastDate = MAXX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] ) VAR __LastScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __LastDate ), [Score] ) RETURN __LastScore ), "__Diff", [__LastDateScore] - [__FirstDateScore] ) VAR __Result = COUNTROWS( FILTER( __Table, [__Diff] > 0 ) ) RETURN __Result Total Percent Increased Measure = DIVIDE( [Total Icreased Measure], COUNTROWS(DISTINCT('Table'[Customer ID])), 0) Average Appointments Increased Measure = VAR __Table = ADDCOLUMNS( ADDCOLUMNS( DISTINCT( 'Table'[Customer ID] ), "__FirstDateScore", VAR __CustomerID = [Customer ID] VAR __FirstDate = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] ) VAR __FirstScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __FirstDate ), [Score] ) RETURN __FirstScore, "__LastDateScore", VAR __CustomerID = [Customer ID] VAR __LastDate = MAXX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] ) VAR __LastScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __LastDate ), [Score] ) RETURN __LastScore ), "__Diff", [__LastDateScore] - [__FirstDateScore] ) VAR __IncreasedCustomers = DISTINCT( SELECTCOLUMNS( FILTER( __Table, [__Diff] > 0 ), "__ID", [Customer ID] ) ) VAR __Result = AVERAGEX( SUMMARIZE( FILTER( 'Table', [Customer ID] IN __IncreasedCustomers ), [Customer ID], "__Count", COUNTROWS('Table') ), [__Count] ) RETURN __Result eyJrIjoiZmYxMTUxYjUtMTg2Ni00OTcyLWFkMzQtMmY0ZDBhMmYzMTFkIiwidCI6Ijg3NDlmOWI5LWYzMmQtNDdhMS1hMjI0LTM2OTQxOGFlMmY1MSJ9
