totals in rows

[jostro]

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?

[HotChilli]

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

Add a column like the above

[jostro]

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]

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

[jostro]

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]

any idea?

[Anonymous]

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

[jostro]

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]

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

Best

Darek

[Anonymous]

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