Table totals not summing column

[Anonymous]

I have a measure, listed below, that accurately calculates totals at the user level. However, I dont want the measure to apply in the totals at the bottom of the table. The measure looks at "Type" to determine how to calculate and its giving me an incorrect total as a result because type appears in the total as well. I just want the total to be a sum of the column, no other logic applied. Is there a way to just have the totals just sum everything in the column or is an adjustment to the measure needed?

Available Hours =

VAR NonFTE = [Total Hours]

VAR FTE = [Period Hrs old]-[IV Holiday]

Return

IF(
SELECTEDVALUE(time_card_by_day_with_cost[Employee Type])="FTE"
|| SELECTEDVALUE(time_card_by_day_with_cost[Employee Type])="MGR",
FTE,NonFTE)

[Greg_Deckler · Accepted solution]

@Anonymous First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, 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

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[Anonymous]

I fixed this issue, field in the table was similar but not the exact field being used in the summarization and included a few extra users not included in the visual. Changed the field and totals match, individual rows match, and export matches. Thank you all that assisted here!

[Anonymous]

Thanks Greg, this works so much appreciated.

However, my big issue now is that when I export the table (Table that has multiple filters on it), the totals being displayed dont match the totals in the exported excel file. Any solution here?

[gmsamborn]

Hi @Anonymous 

In your RETURN, you could use HASONEVALUE to determine if it is a detail row or a total.

Available Hours =
VAR NonFTE = [Total Hours]
VAR FTE = [Period Hrs old] - [IV Holiday]
RETURN
	IF(
		HASONEVALUE( time_card_by_day_with_cost[User] ),
		IF(
			SELECTEDVALUE( time_card_by_day_with_cost[Employee Type] ) = "FTE"
				|| SELECTEDVALUE( time_card_by_day_with_cost[Employee Type] ) = "MGR",
			FTE,
			NonFTE
		),
		CALCULATE(
			[Total Hours],
			ALLEXCEPT(
				time_card_by_day_with_cost,
				time_card_by_day_with_cost[User]
			)
		)
	)

The "else" part of that IF is the calculation you want to do if it is the total row.

Your calculation will probably be DIFFERENT.  This is for demonstration purposes.

[Anonymous]

Thanks gmsamborn ,

Unfortunately this doesnt work due to circular references. I do think the HASONEVALUE function is going to be involved in the solution, but the ELSE needs to be a total of the Available Hours, not Total Hours listed as a variable. I feel this should not be as complicated as I'm making it...

[gmsamborn]

If you temporarily replace the "else" with something simple like 1, does the circular reference problem go away?  (This would rule out [Name] as being part of the circular reference FWIW.)

[Anonymous]

Yes it does, so the logic does work. Its just a matter of replacing that 1 with the total of the measure itself...

[gmsamborn]

like this?

Available Hours =
VAR NonFTE = [Total Hours]
VAR FTE = [Period Hrs old] - [IV Holiday]
RETURN
	IF(
		HASONEVALUE( time_card_by_day_with_cost[User] ),
		IF(
			SELECTEDVALUE( time_card_by_day_with_cost[Employee Type] ) = "FTE"
				|| SELECTEDVALUE( time_card_by_day_with_cost[Employee Type] ) = "MGR",
			FTE,
			NonFTE
		),
		SUMX(
			ALL( time_card_by_day_with_cost[User] )
			[Available Hours]
		)
	)

[gmsamborn]

OK.  Without a model and data, there's not much else I can do here.

Let me know if you have additional questions.