Forum Discussion
Aggregation and square
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?
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.
4 Replies
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