Evaluate an expression across a group in power bi desktop

[Anonymous]

Hi All,

Looking for suggestions to evaluate an expression e.g. Sum(A minus B) for Country, where the value will be the same across quarters:

Quarter	Country	A	B
Q1	Japan	1872	1835
Q2	India	335	726
Q2	Japan	586	871
Q1	USA	1235	1903
Q3	India	710	1935
Q1	Germany	1274	1327
Q2	USA	1647	531

For the above example, the value should be:

Q1	Japan	-248
Q2	India	-1616
Q2	Japan	-248
Q1	USA	448
Q3	India	-1616
Q1	Germany	-53
Q2	USA	448

For Japan, Sum(A) = 2,458 and Sum(B) = 2,706.. and thus Sum(A-B) = -248

(The value will be same if each row is evaluated for A-B and then the final Sum is calculated)

Thanks in advance! 🙂

[AiolosZhao]

Hi @Anonymous ,

You can use below measure directly:

Measure = SUM('Table'[   A]) - SUM('Table'[   B])

Aiolos Zhao

[Anonymous]

Hi AiolosZhao,

Edited the question.. Need to get the same value for country irrespective of other columns.

Have taken Quarter as an example, could be more than that

[AiolosZhao · Accepted solution]

Hi @Anonymous ,

Please try this:

Measure = CALCULATE(SUM('Table'[A]) - SUM('Table'[B]),ALLEXCEPT('Table','Table'[Country]))

Aiolos Zhao