Forum Discussion
Table totals not summing column
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
Super UserHi 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.
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.)
Yes it does, so the logic does work. Its just a matter of replacing that 1 with the total of the measure itself...