Dealing with Measure Totals

[Greg_Deckler]

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.

[Anonymous]

Hi @Ashish_Mathur,

I wouldnt be able to share the file due to client confidential information. I think the issue is in my data model for some reason. I have two fact tables so I had to make use of the userrelationship function, doing testing now to see if that might be the reason causing this mis calculation  

[Anonymous]

i got the same issue, i used the has one filter.

average holding period= IF(  HASONEFILTER(Capex[Fully E. N. Leases & A. Year]),   IF(FIRSTNONBLANK(Capex[Fully E. N. Leases & A. Year],TRUE()) = YEAR(TODAY()), MONTH(TODAY()),  IF(LASTNONBLANK(Capex[Fully E. N. Leases & A. Year], TRUE())  = LASTNONBLANK(Capex[Built Date],TRUE()) ,     12 - VALUE(month(average(Capex[OTHVR Actual Date])))   ,    VALUE(12  ))) ,      SUMX(Capex,12 - VALUE(month(average(Capex[OTHVR Actual Date]))))             )

[Anonymous]

@Greg_Deckler

This is helpful...

Any way to make it scalable such that when users use or dont use a particular field in the matrix table or add multiple fields to the matrix, the measure should be scalable to calculate totals accordingly?

I mean I am trying to avoid writing HASONEVALUE for multiple fields which will not be anyway feasible if user has self service capabilities.

Any suggestions?

Regards

[Greg_Deckler]

There is a new function ISINSCOPE that is supposed to be a "better" way to do this but I think it still suffers from the same issues that you don't like.

https://docs.microsoft.com/en-us/dax/isinscope-function-dax

It's unfortunate but measure totals are just something that end users need to be aware of, there's no foolproof way of keeping them from shooting themselve in the foot. And I am not aware of something like an "ISALL" function that would return TRUE if everything was selected.

I posted an Idea for this: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/36240835-isall-or-istotal-dax-fun...

[Anonymous]

@Greg_Deckler

Thanks for taking time for this.

I was nearly certain there isn't a way to cater this.

Anyway, I will talk to my users on what are the limitations and discuss on ways they can take care of those.

And thanks for posting an idea for the same. I voted for it.

Regards

[Anonymous]

Thanks Greg, you saved the day 🙂

[Greg_Deckler]

@Anonymous, 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

[Anonymous]

Hi my measure totals are not summing up properly, is there a way to use this if and else function with two measures to get the sum correctly?

[Anonymous]

Greg,

ANy thoughts How I can modify my dax expression to fix my totals issue I'm have in a Datagrid.. If I create a measure for each Calculate statment separately, works fine, but I need relect in one measure for the Grid.

It seems like the total in reflecting amounts for all conditions together.

On adds to another level of complexity is that the column MTD_PY_Actuals is a prior Year calc., thoughts?

TD_PY_Variance_Matrix =
(
CALCULATE (
KPI_Finance_Matrix[MTD_Actuals_Matrix] - KPI_Finance_Matrix[MTD_PY_Actuals],
KPI_Finance_Matrix[Group] = "Revenue"
)
)
+ (
CALCULATE (
KPI_Finance_Matrix[MTD_Actuals_Matrix] - KPI_Finance_Matrix[MTD_PY_Actuals],
KPI_Finance_Matrix[Group] = "Gross Margin"
)
)
+ (
CALCULATE (
KPI_Finance_Matrix[MTD_PY_Actuals] - KPI_Finance_Matrix[MTD_Actuals_Matrix],
KPI_Finance_Matrix[Group] = "Operating Expenses"
)
)

[Greg_Deckler]

Check out this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[PaddyEnFrance]

Hi, this is my first post, so apologies if I inadvertently 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... 

I would love if anyone could break my deadlock..

Thanks

Paddy

[Ashish_Mathur]

Hi,

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

[Anonymous]

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. 

[Greg_Deckler]

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

[Anonymous]

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

[dgenatossio]

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?

[OKgo]

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]

Hi,

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

[Anonymous]

Hi

totals wrongly showing in matrix visual

Can you just help me to create the measure for ''average of Totals''.Row headers are place name.Thanks

[Anonymous]

https://community.powerbi.com/t5/Desktop/Sum-of-values-in-a-measure-with-divide-measure/m-p/296768#M...

Refer this solution for wrong total.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[OKgo]

I'd like those 86 values to be 82.