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. eyJrIjoiODBmNmI4YjItZTMwYi00ZDU4LTg0MWItMzYyZWU3ODk4ZWI4IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
Ticket 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 ActiveTickets
Unable to get the YTD number for a given total
I'm working with a trial balance which gives output in YTD format i.e. it rolls up the numbers from Jan to whichever month you are downloading the data for. Now I want to work our the amounts for the month for which I have written the below dax Below is my Base data named "OS TB_Input" which is granular to the GL and Cost Centre and I have a Dimention table for both GL and Cost centre separately and mapped to this Fact table along with a Calendar table mapped to the Month Column. Amount is the column with YTD numbers for each particular month for that GL/ Cost Centre. "[Trial Balance]" just sums up the amount column which is YTD numbers. Trial Balance = SUM('OS TB_Input'[Amount]) Monthly is a measure created to find the monthly values Monthly = VAR PrevMonth = CALCULATE( [Trial Balance], PREVIOUSMONTH('Calendar'[Date]), COA_Grouping[P&L/ BS] = "P&L" ) VAR CurrentMonth = CALCULATE( [Trial Balance], COA_Grouping[P&L/ BS] = "P&L" ) RETURN IF( ISBLANK(CurrentMonth), BLANK(), [Trial Balance]- PrevMonth ) Although this gives the monthly numbers correctly but it messes up the totals as what I expect in the total yearly column is the sum of all the Months whereas it's showing up a much different number. Pl help to correct the totals and also if someone could suggest how to improve the dax, that would be of great help.
Total Is Correct but row is not when using ALL
Biggest puzzler yet: I have this measure called [%Need]. The sum total of these percentages needs to be 1 in order to compute correctly. However, it's not, so in order to correct it I need to do a formula like: [%Need]/Sum [%Need] *1. So in order to get sum portion I did: CALCULATE(SUMX(VALUES('Calendar'[calendar date]),[%Need]),ALL('Calendar'[CalendarDate])) This puts 1(I checked it out to 15 0s 1.00000000000000 in each row instead of the correct amount which is 1.0147. 1.0147 however appears in the Total at the bottom. I need 1.0147 in each row so I can have it divided by [%Need]. This probably doesn't need to be included, but just in case the [%Need] measure is: IF([CALendarDATE]>LASTNONBLANK ( 'Calendar'[Calendar Date], [Gross Adds]),[PYGA]/([PYALLGAs]-[GAsLyMAXDateAll]),Blank()) Any idea how to get the correct total in each row?Dynamic 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. eyJrIjoiMmUyZjEzNDgtMWNhNC00OGI0LWE2ZDktNjA2ZmY1ZGVkMDdiIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
