topic Re: Measure Totals, The Final Word in Quick Measures Gallery

Measure Totals, The Final Word

Greg_Deckler — Thu, 01 Apr 2021 20:16:32 GMT

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

Re: Measure Totals, The Final Word

Anonymous — Tue, 23 Oct 2018 13:42:18 GMT

Awesome stuff. I look forward to digging into this more.

Re: Measure Totals, The Final Word

Anonymous — Fri, 26 Oct 2018 00:31:35 GMT

Wow. This also worked with AVERAGEX. Now I just have to understand why it works! :) Thank you so much.

Re: Measure Totals, The Final Word

Anonymous — Fri, 26 Oct 2018 12:01:23 GMT

This has got to be the biggest oversight of Power BI is the lack of an elegent solution to handling totals.

Re: Measure Totals, The Final Word

Anonymous — Mon, 17 Dec 2018 19:41:05 GMT

What happens if the if the "Categories" come from different tables, would the summarize work too?

Re: Measure Totals, The Final Word

Greg_Deckler — Mon, 17 Dec 2018 20:23:34 GMT

Yes, should not matter if the categories come from another table. As long as the things are related properly on the backend in the model, everything should work just the same.

Re: Measure Totals, The Final Word

JosePintor — Thu, 27 Dec 2018 17:39:00 GMT

Hi Greg,

Great stuff,

With drilldown and TOPN the totals doesn't work properly to repent the total of TOPN ative dimension at current drilldown level,

Can you take a look on this link that i published in Power BI Forum:

https://community.powerbi.com/t5/Desktop/MATRIX-GrandTotal-calculation-with-TOPN-multiples-dimensions-in/m-p/586735#M278100

Thanks,

José Pintor

Re: Measure Totals, The Final Word

omarevp — Tue, 12 Feb 2019 16:26:30 GMT

Hi Greg_Deckler. really awesome post! I hope to see more of this kind of posts in this community.

I've got a question for you, this has been killing me for a while.

I have a "Purchases" table, in whic I want to know the "price" of every item I buy. This is possible by making this simple measure:
SUM(table[import]) / SUM(table[Quantity]). Divide the sum of the purchase with the quantity of that item bought. Now, I'm trying to compare "Price this month" with "Price last month" in which I ASUME that, I need to make two different measures to know that:

Price this month = CALCULATE ( SUM(table[import]) / SUM([Quantity]), CalendarTable[Relative_Month]="This month")

Price last month = CALCULATE ( SUM(table[import]) / SUM([Quantity]), CalendarTable[Relative_Month]="Last month")

Now, the next thing I would LOVE to know is "Impact" between those prices, I mean, I need to calculate

Impact = Price this month - Price last month * SUM(table[Quantity])

It works fine in a table visual, because in table, it is showed by item. And if I export that data to a CSV file, and I do the sum by myself, it shows the correct result, attached is the excel file.

BUT, when I see the TOTAL at the end of the visual it is wrong, and I need it as a Card Visual, so when I put that measure in a Card Visual it shows the wrong result and I have no way to use different filters on it. Attached is the current result I have.

Another comments=

- Sometimes, "price this month" or "price last month" is in blank.

- I already tried applying your measure:

c_Impacto real = 
VAR __table = SUMMARIZE(table,table[item],"__value",[Impact])
RETURN
IF(HASONEVALUE(table[item]),[Impact],SUMX(__table,[__value]))
 

I'd really appriciate your help, or another's help, this been killing me for a big while

THANK YOU SO MUCH!

Re: Measure Totals, The Final Word

cosminc — Fri, 29 Mar 2019 16:11:15 GMT

you don't want to know how much time took me to find this treasure:)

Thanks a lot!
Cosmin

Re: Measure Totals, The Final Word

cosminc — Fri, 29 Mar 2019 16:37:56 GMT

Hi again,

i used this and works wonderful

m_Total 1 = 
VAR __table = SUMMARIZE('Table1',[Name],"__value",[m_Single])
RETURN
IF(HASONEVALUE(Table1[Name]),[m_Single],SUMX(__table,[__value]))

but you have any idea how can i obtain a evolution of this measure vs same period last year for the same [Name] items?

i calculated a top 20 for 2019 YTD and i need to compare with same 20 items vs last year same period (not top 20 from last year)

Thanks,

Cosmin

Re: Measure Totals, The Final Word

AlejandroPCar — Sat, 13 Apr 2019 03:43:47 GMT

AMAZING! thanks to you a lot!

Re: Measure Totals, The Final Word

tkrupka — Sun, 21 Apr 2019 14:43:34 GMT

I'm still trying to get this, and I just cannot wrap my head around it.

Re: Measure Totals, The Final Word

tkrupka — Sun, 21 Apr 2019 14:59:39 GMT

Nevermind,

This worked:

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])))

Re: Measure Totals, The Final Word

samiller71 — Thu, 25 Apr 2019 14:10:19 GMT

How can one apply this solution to a measure that references two additional measures, as shown below?

Lost Customers = IF([Total Orders]>0 && [Last Period Orders]<=0 ,1,0)

For your reference, below are the details of the two additional measures:

Total Orders = SUMX(RELATEDTABLE('Mfg Invoice Data-Mar'),'Mfg Invoice Data-Mar'[Quantity])

Last Period Orders = CALCULATE(
SUM('Mfg Invoice Data-Mar'[Quantity]),
DATESBETWEEN(
'Mfg Invoice Data-Mar'[Creation Date].[Date],
DATEADD(LASTDATE('Mfg Invoice Data-Mar'[Creation Date].[Date]),-1*[Selected Period],DAY),
LASTDATE('Mfg Invoice Data-Mar'[Creation Date].[Date])
)
)

I am trying to show a total of Lost Customers but the total is outputting "1".

Re: Measure Totals, The Final Word

mauroanelli — Wed, 22 May 2019 08:57:11 GMT

very helpfull, but there's something missing for me because doesn't work.

i have this measure

Tot Costo/h = CALCULATE(SUMX(hr_analytic_timesheet;hr_analytic_timesheet[Ore])*MAX('Dipendenti Costo/H'[Costo H]))

try with your solution

Tot Costo/h_New =

VAR _table = SUMMARIZE(res_partner;[Partner];"_Value";[Tot Costo/h])

RETURN

IF(HASONEVALUE(res_partner[Partner]);[Tot Costo/h];SUMX(_table;[_Value]))

all the table are related

Re: Measure Totals, The Final Word

maulopez — Fri, 23 Aug 2019 05:16:26 GMT

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! : )

Re: Measure Totals, The Final Word

Anonymous — Tue, 03 Sep 2019 06:47:39 GMT

Hello everyone,

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:

From Interview Scheduled to Offer = IF ( ISBLANK([_Average days to Offer]) || ISBLANK([_Average days to Interview - Scheduled]), BLANK(),([_Average days to Offer] - [_Average days to Interview - Scheduled]))

The purpose of it is to ignore the calculation if the other column is blank and if that applies to give a blank result. Which works perfectly, except for the TOTALS. Is there something I can add to my formula so that the total is blank also unless the same candidate has both values?

The 1st and 3rd column are also calculations that apply to columns that contain whole numbers (number of days from application to Interview or Offer in this case) or null values, that apply to each candidate (as I said one row = 1 candidate)

_Average days to Interview - Scheduled = CALCULATE( AVERAGE([_Days Application to Interview - Scheduled]), FILTER(report, report[_Days Application to Interview - Scheduled] <> 0 ))

_Average days to Offer = CALCULATE( AVERAGE([_Days Application to Offer]), FILTER(report, report[_Days Application to Offer] <> 0 ))

I might be missing something here, can you please help me implement the "HASONEVALUE" function? Maybe it would be a solution here.

Thank you very much!

Re: Measure Totals, The Final Word

calerof — Fri, 22 Nov 2019 13:53:10 GMT

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?

Thanks,

Fernando

Re: Measure Totals, The Final Word

Anonymous — Thu, 09 Jan 2020 16:22:01 GMT

Do the formula work between two tables?

Thanks for your help

Re: Measure Totals, The Final Word

Anonymous — Mon, 17 Feb 2020 03:45:44 GMT

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.