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.

[Ashish_Mathur]

Hi,

Does this measure work?

Measure1 = SUMX(VALUES(OBJ_TAB[UPC]),[Cases Needed])

[Anonymous]

Thanks!!! Is working 🙂 @Ashish_Mathur 

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[Anonymous]

Hi, 

I'm also experiencing total value issues.. I'm using this function in a measure:

BonusTest = 
VAR BonusMin = SELECTEDVALUE('BonusSpecs'[BonusMin])
VAR BonusMax = SELECTEDVALUE('BonusSpecs'[BonusMax])
VAR BonusValueMin = SELECTEDVALUE('BonusSpecs'[BonusValueMin])
VAR BonusValueMax = SELECTEDVALUE('BonusSpecs'[BonusValueMax])
RETURN
SWITCH(
TRUE()
,SUM(Income[Value]) < BonusMin, 0
,SUM(Income[Value]) < BonusMax, BonusValueMin
,BonusValueMax
)

And as you can see, the total value is incorrect:

Although I'm not sure on how to use HASONFILTER in my case, any tips?

[Alex_R]

Hi all
I spent days to solve my measure totals issue and the solution from @Greg_Deckler  seems to be the closest one to fit my expectations.
However, I don't quite get how to use the HASONEFILTER function in my case correctly and hope you could help.

I have got a table visualization, where only Sales is a table column, while Sales Prev, Sessions and Sessions Prev are measures.

Country	Sales	Sales Prev	Sessions	Sessions Prev
uk	1000	500	10000	8000
us	2000	1000	15000	12000
de	3000
fr	2000	500	12000	10000
Total	8000	2000	49000	40000

Sales Prev calculates the sum of Sales within the previous period, which is being selected on a special data slicer for previous period.
If there were no sales in the country during the previous period, I get a blank value which is totally OK.

However, if there were no sales during the previous period, I don't need to show Sessions for such countries neither for the current nor for the previous period.
I made it possible by creating the following measure for Sessions:

Sessions = IF(ISBLANK(sales_table[sales]),BLANK(),
CALCULATE(SUM(sessions_table[sessions])))

Although this measure hides the Session count for countries with no sales in the previous period, the Total is calculated for ALL rows including the hidden. For example, it calculates the Total of both Session columns including DE, which had no Sales within the previous period, but I don't actually need it in this case.

How should I modify the Session measure to avoid including the hidden values into the calculation of Totals?

[Nikola123]

yyyyyeeeez man, saved my life and my monitor! 

Thank you, This was bothering me for a month!

[Anonymous]

@Greg_Deckler  I am thinking that this is going to be the solution to what I'm facing as well, but wonder if you can assist on how I'd integrate HASONEVALUE in to the measure I'm working with:

YTD Actuals =

CALCULATE(

SUM([ytd actuals]),

ALL('project '[active]),

FILTER('_fact_date',[ytd_dates] = 1)

)

Any thoughts?

[Greg_Deckler]

@Anonymous Hard to tell, I would need to understand how your visualization is laid out. This article might get you where you need to be however: 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

[Anonymous]

Many kudos, @Greg_Deckler ! That is one of the most useful solutions I have come across all year. Thank you!

[sivashankr]

Can you able to help us identify and fix the issue for this  formula ?

showing the wrong summation for the total in the below calucation

ValueWeightedAverage =
VAR
NetValTemp= CALCULATE(LASTNONBLANK(InvoicesAll[ITEMNETVALUE],1))
VAR
Netval = CALCULATE(sum(InvoicesAll[ITEMNETVALUE]))
VAR
Days=[_SCdateDiff]
VAR
VWA=DIVIDE(NetVal,[_CustCurrTot],0)
RETURN
VWA

power biexcel manaul- required this value

pbix file:

https://onedrive.live.com/?authkey=%21AMrOI0O9c72Xs%2Dw&cid=C007331E59E3588D&id=C007331E59E3588D%211...

[DiegoSDEOAV]

Hi,

I have an issue with the total, I had two columns, the first one its the value and the second one takes that value and multiplies it aoccording his % of participation, in the first colum the total works great, but for the second column its taking the average of the % of participaction and then sum all the values. 

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

Value_unit  = IF(SELECTEDVALUE('Unit'[Value])="One",sum(Table1[Detail.qty]),if(SELECTEDVALUE('Unit'[Value])="Two",(sum(Table1[Detail.qty]) * SUM('CG'[RET_Size] )),IF(SELECTEDVALUE('Unit'[Value])="Three",(sum(Table1[Detail.qty]) * SUM('CG'[RET_Size]) * SUM('CG'[RET_One]))/10)))

could any one please modify the above measure to get correct subtotals by using hasonevalue or hasonefilter or any other..

[Anonymous]

share dataset and your expected result

[Anonymous]

expected output

[Anonymous]

i am confused with your requirement. I suggest you to do calculation using SUMX and Values

[Anonymous]

@Greg_Deckler 

Could you please help on this https://community.powerbi.com/t5/Desktop/Distinct-Count-measure-Grand-Total-is-showing-Wrong-Value/m...

[Anonymous]

Hi,

Im not sure if this is the right subject to be asking this but Im in desperate need of a solution. 

I have an issue with the totalling of my Matrix, for some reason it is not calculating it correctly. I have tried using the sumx function but still no luck.  If I calculate the values for each row I get to 55 but on the matrix it is 53.  Please assist.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

Hi @Ashish_Mathur , do you want me to share the file with you or just screenshots?

[Ashish_Mathur]

The PBI file.