cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User IV
Super User IV

Dealing with Measure Totals

This one has come up quite a bit recently. The issue surrounds using Measures in Table visualizations with a Total row. The complaint is that the "Total" row is "wrong" for the measure. Technically, the total row is correct for the measure, it's just not what most people expect. What people expect is for the "Total" to display the sum of the values in the column. Measures do not do this. Measures respect the context of the Total row and is calculated within that context. Therefore, a Measure used in a column in a table visualization will likely have an unexpected value in the Total column.

 

There are a couple ways of fixing this. The easiest is to turn off the Total row.

 

Assuming that is not what you want, you can use the HASONEFILTER function to get around this issue. However, the ultimate solution will depend on how your measure is calculated.

 

For example, given the following data:

 

Year Amount

Year1500
Year21500
Year32000
Year4100
Year5800

 

We wish to find the total extra Amount spent above 1000 for each year. If the amount is not over 1000, we wish to display 0. To this end, we create a measure:

 

MyMeasure = IF(SUM(Table[Amount])<1000,0,SUM(Table[Amount])-1000) 

Adding this to a Table visualization along with Year, we get the correct answer for each of the rows, but the Total line displays 3900, not 1500 as we would expect. The figure 3900 is calculated because the Measure is performing its calculation for ALL of the rows in the table, so the calculation is (500 + 1500 + 2000 + 100 + 800) - 1000 = 3900.

 

Correct, but not what was expected.

 

To get around this problem, use HASONEFILTER to calculate the Measure one way within a row context and another way within the Total row context, such as:

 

MyMeasure2 = IF(HASONEFILTER(Table[Year]),
IF(SUM(Table[Amount])<1000,0,SUM(Table[Amount])-1000),
SUMX(FILTER(Table,[Amount]>1000),[Amount]-1000)
)

Breaking this down, we essentially wrap our original measure in an IF statement that has the HASONEFILTER function as the logical test. If HASONEFILTER equals true, we calculate our Measure as before. However, if HASONEFILTER is false, we know that we have a Total row and we calculate our Measure a different way.

 

 

 

 

 

 

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




46 REPLIES 46

Are you able to provide a visual of what this looks like when it's completed? 

A really useful write-up, and one I have used previously.

I have a strange issue where using HASONEFILTER or HASONEVALUE has not worked for me in calcualting the totals I would expect. See my post here, if anyone can help me: https://community.powerbi.com/t5/Desktop/Incorrect-Measure-Total/m-p/454679#M210659

Just an update to my cry for help above!

I was given a solution that worked for me that involved having a SUMMARIZE function wrapped within my SUMX function. It could be useful if anyone else comes upon this guide, but is still having an issue with what they expect the total to be.

 

http://community.powerbi.com/t5/Desktop/Incorrect-Measure-Total/m-p/454679#M210659

Advocate II
Advocate II

Dude, this by far has been my most frustrating experience working with Power BI is to get the totals row for a table to calculate measures as I'd expect.

 

Thanks for the tip

Resolver I
Resolver I

I believe I understand how it works but I do not know how to get it rto work with Division.

 

I need to multiply Sales x Discount. I have a Measure Discount Amount that is correct on the rows. The Total would have to be a weighted average calculation as Sales and Discount Amounts are different across the Clients. 

 

 

Would need to see some sample data and what you expect as output. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Frequent Visitor

Hi, this is my first post, so apologies if I inaScreenshot (18).pngdvertently do something wrong. I get the HASONEFILTER trick, but I cannot resolve my formula for the end. I would like my total to be a simple sum of the values above. My example is a purchase price variance sheet.

 

The basic maths for the rows is Qty x Price, average prices CY vs PY, Qty variance (in Kg), Value Variance (in €) which then breaks out into a "Volume Valued Variance" and then a "Price Variance". I would like the column totals of the PPV column & Vol_Val_Var column to equal the sum of the rows above. It seems like a simple question, but in all of my trawling the solution does not jump out at me... 

 

 

Screenshot (19).png

 

 

 

 

 

I would love if anyone could break my deadlock..

Thanks

Paddy

Hi,

 

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Frequent Visitor

Thanks Greg, you saved the day 🙂

@joydeep7, Awesome! Glad to hear it. I also just posted Measure Totals, The Final Word, that provides a general solution to the problem.

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors