- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Dealing with Measure Totals

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Greg_Deckler

Super User

Dealing with Measure Totals

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

08-29-2016
12:18 PM

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

Year1 | 500 |

Year2 | 1500 |

Year3 | 2000 |

Year4 | 100 |

Year5 | 800 |

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.

Proud to be a Datanaut!

25 REPLIES 25

Michiel

Regular Visitor

Re: Dealing with Measure Totals

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

08-30-2016
12:36 AM

I tend to avoid measures that are designed with a specific visualization in mind, but indeed, in the case of non-standard totals it's inevitable. In your example, the assumption is that the years are the identifiers for the rows in the table. When an additional level, e.g. Month, is added, then the behaviour of the measure will be - different.

But this aside, the part of your formula for the total would work just as well on the detail rows when iterating over VALUES(Table[Year] instead of Table itself:

MyMeasure3 = SUMX(FILTER(VALUES(Table[Year]),[Amount]>1000),[Amount]-1000)

On a detail row, VALUES(Table[Year]) would contain only one row and the filtered table is empty when [Amount]<=1000. This means that rows for years with [Amount] lower than 1000 will have a blank value. If you do want to have 0 instead of blank, just add 0 to the result:

MyMeasure3 = SUMX(FILTER(VALUES(Table[Year]),[Amount]>1000),[Amount]-1000) + 0

quratzafar

Frequent Visitor

Re: Dealing with Measure Totals

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

09-04-2016
10:48 PM

This was quite informative, thanks.

OKgo

Regular Visitor

Re: Dealing with Measure Totals

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

05-15-2018
07:52 AM

Great post. It give insight into why PBI is the way it is. My attempt to apply the knowledge above is failing. This matrix has muliple at a page level so the HASONEFILTER is not working out? The HASONEVALUE is not working out for me either.

Here is the measure. Edit tips appreaciated. (Credit to @Ashish_Mathur)

Forecast = if(MIN('Calendar'[Date])<[First month in which data is available],BLANK(),if(EOMONTH(MAX(Workfront[Due On]),0)>=EOMONTH(MAX('Calendar'[Date]),0), CALCULATE([3 month av],DATESBETWEEN('Calendar'[Date],[Month till where data is available],[Month till where data is available])),BLANK()))

Ashish_Mathur

Super User

Re: Dealing with Measure Totals

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

05-15-2018
04:12 PM

Hi,

What result are you looking for? What is your data? Explain.

OKgo

Regular Visitor

Re: Dealing with Measure Totals

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

05-15-2018
04:23 PM

I'd like those 86 values to be 82.

Ashish_Mathur

Super User

Re: Dealing with Measure Totals

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

05-15-2018
05:28 PM

Hi,

I really do not know how you arrive at 82. It is defenitely not a summation of the numbers in the column above. Put in some more effort and explain your data/question.

OKgo

Regular Visitor

Re: Dealing with Measure Totals

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

05-15-2018
05:33 PM

Edit: The red 86 should be 82 for July & August. As one of the proejects have ended (now blank). The total is in matrix > subtotals > row sub totals.

OKgo

Regular Visitor

Re: Dealing with Measure Totals

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

05-16-2018
09:18 AM

Sorry for all the posts. Sending form my cell phone browswer was a nightmare. Here is a sample file

https://www.dropbox.com/s/j144i4hfe8k5su0/PowerBi%20Timesheet%203.pbix?dl=0

It would be great if there was only one subtotal row that respected the data. For example 2017-07 would be 45.34 not 78.67

dgenatossio

Frequent Visitor

Re: Dealing with Measure Totals

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

05-30-2018
07:38 AM

My situation is different because I have 3 tables. One is a table with employee IDs (Table 'ID'). One is a table with employee IDs and their 2017 sales for one business (Table 'A'). The third table has employee IDs and their 2017 sales for the other business (Table 'B'). Tables A and B are connected to the employee table by the employee ID. The employees are not exactly the same for each business, though there is some overlap where the employee ID could appear on A and B. I have columns on the ID table that are used as filters on the page, based on where the employee is located and how long they have been at the company.

I want to calculate the overlapping 2017 results. I have created a measure on the ID table that says this:

Overlap = if(SUM('A'[Sales])>0,SUM('B'[Sales]),0)

Basically: if the employee had Sales for A, give me their sales for B

It works for each ID on the table and the row shows 0 for the row if sales for A were 0, but the Grand Total returns the entire Sales for B (not just the sum of the sales for IDs that had more than 0 sales for A).

The post is a bit different because mine is returning the value from table B if the sum of the values on table A is more than 0. Any ideas on how to get the total to work correctly?