Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Table totals not summing column

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...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
gmsamborn
Icon for Super User rankSuper User
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

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

    • gmsamborn's avatar
      gmsamborn
      Icon for Super User rankSuper User
      2 years ago

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

       

      Let me know if you have additional questions.

    • gmsamborn's avatar
      gmsamborn
      Icon for Super User rankSuper User
      2 years ago

      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]
		)
	)