Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
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
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!!!)
any idea?
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
If your table is really big, the solution by @HotChilli will be agonizingly slow due to the number of context transitions.
Best
Darek
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
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |