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. eyJrIjoiODBmNmI4YjItZTMwYi00ZDU4LTg0MWItMzYyZWU3ODk4ZWI4IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Customer Retention Part 3: Period Of Stay – Cohort Analysis
Objective: Period Of Stay – Cohort Analysis provide visibility on how many customers were retained after their first date of purchase. Cohort Analysis is studying the behavioral analysis of customers. Assume there are 100 new customers (consumers who made the first purchase in the store) in Jan 2020. Out of these 100, how many customers came back in the second month (Feb 2020). Then how many returned in the third month (March -2020) and so on for every month in 2020. Columns First Sales = minx(FILTER(Sales,[Customer Id] =EARLIER([Customer Id])),[Sales Date]) Customer Age = DATEDIFF([First Sales],[Sales Date],MONTH)+1 Table Customer Age Bucket = ADDCOLUMNS(GENERATESERIES(1,max(Sales[Customer Age])+1) ,"Age in Month" , "Month " &[Value]) Measures Customers = DISTINCTCOUNT(Sales[Customer Id]) Retain % = CALCULATE(divide(DISTINCTCOUNT(Sales[Customer Id]),CALCULATE(DISTINCTCOUNT(Sales[Customer Id]),ALLSELECTED('Customer Age Bucket') , 'Customer Age Bucket'[Age] =1)) , 'Customer Age Bucket'[Age] >1) eyJrIjoiYWM4MGY3ZTUtZmZhZS00ZDQ4LWE1NzUtMGUwMDc3N2U4MmI0IiwidCI6ImVhOGJkMWZkLWFjMzQtNGFlMi1iNDIxLTZjZmEyZmNmZjI0MyJ9
