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 ...
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.
why does the total mismatch?
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
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.