# Measure Totals, The Final Word

10-21-2018 06:09 AM - last edited 10-21-2018 06:22 AM

# Measure Totals, The Final Word

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-21-2018 06:09 AM - edited 10-21-2018 06:22 AM

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

**Did I answer your question? Mark my post as a solution!**

Proud to be a Datanaut!

## Re: Measure Totals, The Final Word

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-23-2018 06:42 AM

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

## Re: Measure Totals, The Final Word

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-25-2018 05:31 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-26-2018 05:01 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-17-2018 11:41 AM

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

## Re: Measure Totals, The Final Word

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-17-2018 12:23 PM

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.

**Did I answer your question? Mark my post as a solution!**

Proud to be a Datanaut!

## Re: Measure Totals, The Final Word

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-27-2018 09:39 AM

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:

Thanks,

José Pintor

## Re: Measure Totals, The Final Word

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-12-2019 07:56 AM - edited 02-12-2019 08:26 AM

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!