cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

1 ACCEPTED SOLUTION
Super User

@BDale93 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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
9 REPLIES 9
Super User

@BDale93 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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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!

Helper I

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?

Super User

Hi @BDale93

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.

Helper I

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...

Super User

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

Helper I

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

Super User

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

Super User

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

Let me know if you have additional questions.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors