Forum Discussion

Tan_LC's avatar
Tan_LC
Icon for Helper II rankHelper II
3 years ago
Solved

Average in DAX

Dear all,   In below table, I would like to calculate the Average using DAX and the desired result should be same as "Average in Excel". However, when doing calculation in DAX Power BI, the result...
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, Tan_LC 

     

    You can try the following methods.

    Measure = 
Var _N1=CALCULATE(COUNT('Table'[Line]),ALLEXCEPT('Table','Table'[Shift],'Table'[Line]))
Var _N2=CALCULATE(DISTINCTCOUNT('Table'[Shift]))
Return
DIVIDE(_N1,_N2)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity Support
3 years ago

Hi, Tan_LC 

 

You can try the following methods.

In the Power Query, Transpose-Unpivot Columns

Column = CALCULATE(AVERAGE('Table'[Value]),ALLEXCEPT('Table','Table'[Line]))

Or

Measure = CALCULATE(AVERAGE('Table'[Value]),ALLEXCEPT('Table','Table'[Line]))

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

Tan_LC's avatar
Tan_LC
Icon for Helper II rankHelper II
3 years ago

v-zhangti 

Thanks on the solution given but it still giving me the incorrect outcome. Perhaps, you can have a look on the raw data I reply to ERD  for details.

Thanks.