Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
BDale93
Helper I
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)

BDale93_0-1696266020999.png

 

 
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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!

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

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

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

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

 

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

 

Let me know if you have additional questions.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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