Aggregation and square

[Anonymous]

Hi,

I am trying to aggregate my data and then square, while PBI first squares and then aggregates as follow:

Input
id1	id2	value	sqvalue
1	10	100	10000
1	10	200	40000
1	20	300	90000
1	20	400	160000
2	30	300	90000
2	20	600	360000
2	30	200	40000
2	30	800	640000
3	20	100	10000
3	10	200	40000
3	10	100	10000

PBI output:

PBI output
id1	id2	value	sqvalue
1	10	300	50000
1	20	700	250000
2	20	600	360000
2	30	1300	770000
3	10	300	50000
3	20	100	10000
			1490000

Desired output

Desired output
id1	id2	value	sqvalue
1	10	300	90000
1	20	700	490000
2	20	600	360000
2	30	1300	1690000
3	10	300	90000
3	20	100	10000
			2730000

is there a way to set precedence on calculation?

[harshnathani]

Hi @Anonymous ,

Create a measure.

sumid2 =

var _a = CALCULATE(SUM('Table'[value]),FILTER(ALLEXCEPT('Table','Table'[id2]), 'Table'[id1] = MAX('Table'[id1])))

RETURN

_a*_a

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

why does the total mismatch?

[Anonymous]

Another example:

input:

id1	id2	value1	value2	difference	diff_sq
1	10	100	50	50	2500
1	10	200	40	160	25600
1	20	300	50	250	62500
1	20	400	30	370	136900
2	30	300	20	280	78400
2	20	600	40	560	313600
2	30	200	20	180	32400
2	30	800	50	750	562500
3	20	100	50	50	2500
3	10	200	20	180	32400
3	10	100	40	60	3600

PBI output

Desired
id1	id2	difference	diff_sq
1	10	210	44100
1	20	620	384400
2	20	560	313600
2	30	1210	1464100
3	10	240	57600
3	20	50	2500

now power bi  adds 2500+25600 = 28100 instead of 210^2 = 44100

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, you may create a measure as below. The pbix file is attached in the end.

Result = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[id1],
    'Table'[id2],
    "diff",SUM('Table'[difference ])
)
var newtab = 
ADDCOLUMNS(
    tab,
    "sqdiff",
    POWER([diff],2)
)
return
SUMX(
    newtab,
    [sqdiff]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.