Forum Discussion

sebasjun's avatar
sebasjun
Icon for Helper I rankHelper I
8 years ago
Solved

SUMX with calculate average

Hello

 

I need apply this function 

 

 

 

I created a simple datatable

 

 

 

AVGx = AVERAGE(Table1[x]) 

Measure Manual = SUMX(Table1; Table1[x] - 14)

Measure Calc = SUMX(Table1;(Table1[x] - [AVGx]))

 

Mesaure Manual is my desired result and Measure Calc is my automated measure

 

The problem is in Measure Calc this  caculate the average per each row and is the same wth x and the result is 0 normal!, i need to calculalte the average total in the context filter (Cliente = C1) --> 14  

 

Thanks

Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    Hello sebasjun

    Try this - storing the average in a variable. Previously when the [AVGx] measure was computed in the context of each row, context transition meant that it was just averaging the value on that row.

    Measure Calc =
VAR Avg = [AVGx]
RETURN
  SUMX ( Table1; ( Table1[x] - Avg ) )
  • Stachu's avatar
    Stachu
    8 years ago

    I think the average has to be either with ALL or ALLSELECTED

    AVGx:=CALCULATE(AVERAGE(Table1[x]),ALLSELECTED(Table1[x]))

     

5 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    8 years ago

    Hello sebasjun

    Try this - storing the average in a variable. Previously when the [AVGx] measure was computed in the context of each row, context transition meant that it was just averaging the value on that row.

    Measure Calc =
VAR Avg = [AVGx]
RETURN
  SUMX ( Table1; ( Table1[x] - Avg ) )
    • sebasjun's avatar
      sebasjun
      Icon for Helper I rankHelper I
      8 years ago

      Hello

       

      Thanks for you reply, I try and is 0 

       

      I attached the file if you can help me  File

      • Stachu's avatar
        Stachu
        Icon for Community Champion rankCommunity Champion
        8 years ago

        I think the average has to be either with ALL or ALLSELECTED

        AVGx:=CALCULATE(AVERAGE(Table1[x]),ALLSELECTED(Table1[x]))

         

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi sebasjun,

     

    Change measure [AVGx] to a calculate column [AVGx Column] them modify [Measure Calc] using DAX like this:

     

    Measure Calc = VAR XXX = MAX(Table1[AVGx Column]) RETURN SUMX ( Table1, ( Table1[x] - XXX ) )

    The result is as below:

     

    Regards,

    Jimmy Tao