10-21-2018 06:09 AM - last edited 10-21-2018 06:22 AM
With apologies to Theodor Geisel...
Measure totals have you perturbed?
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.
Trial = IF(ISFILTERED(GeneratorNames[GeneratorName])&&ISFILTERED(RunData[Date])&&ISFILTERED(Locations[Location])&&ISFILTERED(IBXs[IBX_Name]), SUMX(RunData,[Engine Run Time]*SUMX(GeneratorNames,GeneratorNames[FuelRate])), VAR __table = SUMMARIZE(RunData,Locations[Location],IBXs[IBX_Name],GeneratorNames[GeneratorName],RunData[Date],"__value",SUMX(RunData,[Engine Run Time]*SUMX(GeneratorNames,GeneratorNames[FuelRate]))) RETURN IF(ISFILTERED(GeneratorNames[GeneratorName])&&ISFILTERED(RunData[Date])&&ISFILTERED(Locations[Location])&&ISFILTERED(IBXs[IBX_Name]), SUMX(RunData,[Engine Run Time]*SUMX(GeneratorNames,GeneratorNames[FuelRate])),SUMX(__table,[__value])))
How can one apply this solution to a measure that references two additional measures, as shown below?
very helpfull, but there's something missing for me because doesn't work.
i have this measure
Amazing! I just tried this Greg's solution after days of struggling with meassure totals for the first time and it worked perfectly. It is indeed amaizing that Microsoft hasn't come out with a solution of their won for this problem alredy coded in PBI, but this solution is a real wonder. Thanks so much Greg, you're a genious ! BTW, the poem is fantastic too! : )
I am trying to calculate some average times and it works well, except the Totals. Each row represents a candidate.
The table I have looks like this:
The formula I use for the column in the middle is:
Hi @Greg_Deckler ,
I have a question regarding the use of a VAR in the summarization. In that case the result is wrong. I have to use a VAR due to a complex commission calculation based on different levels of revenue and % margin.
For instance, if revenue > $250,000 and margin % is less than 56% but greater than 52% the commision % is 1%,
with the same revenue limit if margin is less than 60% but greater than 56% the commission % is 1.5%, etc.
To handle this calculation I created VAR as follows:
Comision = VAR Sales= [Sales Total] VAR Margin = [Margin Total] VAR Commission = SWITCH( TRUE(), Sales >= 200000 && Sales < 250000, 0, Sales>= 250000 && Margin >= 0.52 && Margin < 0.56, 0.01, Sales>= 250000 && Margin < 0.60, 0.015, Sales>= 250000 && Margin >= 0.6, 0.02, 0 ) VAR BaseBonus = SWITCH( TRUE(), Sales>= 200000 && Sales < 250000, 1000, 0 ) VAR CommissionAmount = Sales * Commission + BaseBonus VAR ComisTotal = SUMMARIZE( VALUES( OCRD[SlpCode] ), OCRD[SlpCode], "ComisTot", MAX( 0, CommissionAmount ) ) RETURN SUMX( ComisTotal, [ComisTot] )
At using a VAR after RETURN it gives a wrong total.
I made a dummy data model to show this procedure here.
Could you please advice?
Thank you Greg. I was able to obtain the correct totals for a simple (yet critical) sum of absolute values calculation in Power Pivot by adapting your technique. What I thought would be a three second "wrap in ABS" formula turned into a three (12-hour) day nightmare. Cannot believe what I just went through to achieve what is literally the dead-simplest calculation mathematically possible. But can't thank you enough for this solution.