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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jostro
Frequent Visitor

totals in rows

Hi, I'm searching for an performant solution for the following problem:

Key      Material    Component   Amt    Flag  Sum
4444    111           1                    100     X       400
4444    111           2                    200              400
4444    111           3                    300     X       400

I would like to sum the amount of all components with Flag = 'X'. And the value should be available in each row. I that possible?

9 REPLIES 9
HotChilli
Super User
Super User

Column = CALCULATE(SUM(Table1[amt] ), ALL(Table1), Table1[flag] = "X")

Add a column like the above

This is working fine. The next step will be to simulate the flag. That means that for one of the components the "X" should be not "X". For this I created a filter for the users. When they select for example the first component, the sum should be different as before.

 

ComponentSimulate
1       
2
3

Key      Material    Component   Amt    Flag  Sum
4444    111           1                    100     __      300
4444    111           2                    200     __      300
4444    111           3                    300     X       300

Anonymous
Not applicable

Mate, it's not a column you needed but a measure... A COLUMN ONCE CALCULATED IN DAX CANNOT BE CHANGED.

 

Let @HotChilli deal with this.

 

Best

Darek

my idea was to do it with filtering:

 

… in my words:       if flag = X and material <> ComponentSimulate then sum(Amt)

 

 

ComponentSimulate
1       
2
3

Key      Material    Component   Amt    Flag  Sum
4444    111           1                    100     X       300
4444    111           2                    200               300
4444    111           3                    300     X       300

... but I have problems to see the 300 in every row (as total it is working!!!)

jostro
Frequent Visitor

any idea?

Anonymous
Not applicable

Mate, nobody wants to help you because nobody really understands what you want... When you post a question, you have to be maximally clear about your intentions and the outcome. Otherwise, you won't get any replies. Easy as that.

 

Best

Darek

thanks for the reponse. My problem is not the filter, but to get the sum in all of the rows. I will try the solutions

Anonymous
Not applicable

If your table is really big, the solution by @HotChilli will be agonizingly slow due to the number of context transitions.

 

Best

Darek

Anonymous
Not applicable

Everything is possible.

 

AmtWithFlagX = -- this is a calculated column, not a measure
var __currentKey = T[Key]
var __currentMaterial = T[Material]
var __sumWithFlagX =
	SUMX(
		filter(
			T,
			T[Material] = __currentMaterial,
			T[Key] = __currentKey,
			T[Flag] = "X"
		),
		T[Amt]
	)
return
	__sumWithFlagX

Best

Darek

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.