Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
What we want to achieve is shown in the image.
If the Code is "AAA", the "Value" is aggregated with the same Num2 (e.g. B731 or B867).
For example... Code is AAA and Num2 is B731.
Code is AAA and Num2 is B731 has two Values ("100" and "-100").
When the two "Values" are aggregated, the result is 100+(-100), so the result is 0.
We want to output this "0" result to the two rows of B731.
If Code is not "AAA", the data is totaled by "Num1".
In summary, I think it goes like this.
Is it possible to achieve this with a major? Or is there some way to have it calculated in a computed column?
I tried to group them in the Power Query Editor, but It did not work.
If anyone has any good ideas or methods, please let me know.
Best regards, Lopez
Solved! Go to Solution.
Hi @Lopez0090
Thanks for reaching out to us.
>>
In summary, I think it goes like this.
You can try this,
Column =
IF (
'Table'[Code] = "AAA",
SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[Num2] = EARLIER ( 'Table'[Num2] ) ),
[Value]
),
SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[Num1] = EARLIER ( 'Table'[Num1] ) ),
[Value]
)
)
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @Lopez0090
Thanks for reaching out to us.
>>
In summary, I think it goes like this.
You can try this,
Column =
IF (
'Table'[Code] = "AAA",
SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[Num2] = EARLIER ( 'Table'[Num2] ) ),
[Value]
),
SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[Num1] = EARLIER ( 'Table'[Num1] ) ),
[Value]
)
)
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Thank you very much.
Actually, I need to use a dynamically calculated Value (measure) at [Value], and I would like to express this expression in a measure, is it possible to create it in a measure?
Hi @Lopez0090
Thanks for your reply.
You can try this
measure=
IF (
min('Table'[Code] )= "AAA",
SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[Num2] = min( 'Table'[Num2] ) ),
[Value]
),
SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[Num1] = min( 'Table'[Num1] ) ),
[Value]
)
)
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
My English is not very good, so please let me know if I am not very clear.
User | Count |
---|---|
128 | |
72 | |
70 | |
58 | |
53 |
User | Count |
---|---|
192 | |
96 | |
67 | |
64 | |
54 |