topic Re: totals in rows in DAX Commands and Tips

totals in rows

Anonymous — Tue, 16 Jul 2019 15:05:16 GMT

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?

Re: totals in rows

Anonymous — Tue, 16 Jul 2019 15:27:41 GMT

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

Re: totals in rows

HotChilli — Tue, 16 Jul 2019 15:27:47 GMT

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

Add a column like the above

Re: totals in rows

Anonymous — Tue, 16 Jul 2019 15:30:27 GMT

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

Re: totals in rows

Anonymous — Tue, 16 Jul 2019 15:32:52 GMT

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

Best

Darek

Re: totals in rows

Anonymous — Tue, 16 Jul 2019 15:45:50 GMT

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

Re: totals in rows

Anonymous — Tue, 16 Jul 2019 16:07:04 GMT

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

Re: totals in rows

Anonymous — Tue, 16 Jul 2019 16:18:37 GMT

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

Re: totals in rows

Anonymous — Wed, 17 Jul 2019 10:23:11 GMT

any idea?

Re: totals in rows

Anonymous — Thu, 25 Jul 2019 00:26:32 GMT

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