Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

why does the total mismatch?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Another example:

     

    input:

    id1id2value1value2difference diff_sq
    11010050502500
    1102004016025600
    1203005025062500
    12040030370136900
    2303002028078400
    22060040560313600
    2302002018032400
    23080050750562500
    32010050502500
    3102002018032400
    31010040603600

     

    PBI output

     
    Desired
    id1id2difference diff_sq
    11021044100
    120620384400
    220560313600
    23012101464100
    31024057600
    320502500
     

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

    • v-alq-msft's avatar
      v-alq-msft
      Community Support
      6 years ago

      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.