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.

ERD's avatar
ERD
Icon for Community Champion rankCommunity Champion
3 years ago

Tan_LC , how does your data look like and the measure that gives wrong results?

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

ERD 

I extracted some of the data and share with you as below:

In the Part ID column, each ID is an unique ID and each row represents 1 count.

In the DAX Average calculation, it should reflect correct Ave in accordance with the filter I select regardless of Line, Part ID, Shift or Date. 

 

LineSPMAve in Excel
C124172522
C210201816
C315211015

 

Machine
No.		LinePart No.Part IDShiftDate
101C1HF13532D5S12/2/2023
101C1HF13532D6S12/2/2023
101C1HF13532D7S12/2/2023
101C1HF13532D8S12/2/2023
101C1HF13532DHS13/2/2023
101C1HF13532DIS13/2/2023
101C1HF13532DJS13/2/2023
101C1HF13532DKS13/2/2023
101C1HF13532EEP12/2/2023
101C1HF13532EFP12/2/2023
101C1HF13532EMP13/2/2023
101C1HF13532EOP13/2/2023
101C1HF13532ESP12/2/2023
101C1HF13532ETP12/2/2023
101C1HF13532EUP12/2/2023
101C1HF13532HBM12/2/2023
101C1HF13532HCM12/2/2023
101C1HF13532HDM12/2/2023
101C1HF13532HEM12/2/2023
101C1HF13532HLM13/2/2023
101C1HF13532HMM13/2/2023
101C1HF13532HNM13/2/2023
101C1HF2370D0WS12/2/2023
101C1HF2370D0XS12/2/2023
101C1HF2370D0YS12/2/2023
101C1HF2370D0ZS13/2/2023
101C1HF2370D10S13/2/2023
101C1HF2370D11S13/2/2023
101C1HF2370D12S13/2/2023
101C1HF2370D13S13/2/2023
101C1HF2370D1BS12/2/2023
101C1HF2370D1PP12/2/2023
101C1HF2370D1QP12/2/2023
101C1HF2370D1RP12/2/2023
101C1HF2370D1SP12/2/2023
101C1HF2370D1TP12/2/2023
101C1HF2370D1UP12/2/2023
101C1HF2370D20P13/2/2023
101C1HF2370D21P13/2/2023
101C1HF2370D22P13/2/2023
101C1HF2370D23P13/2/2023
101C1HF2370D7JM12/2/2023
101C1HF2370D7MM12/2/2023
101C1HF2370D7NM12/2/2023
101C1HF2370D7OM12/2/2023
101C1HF2370D7PM12/2/2023
101C1HF2370D7QM13/2/2023
101C1HF2370D7RM13/2/2023
101C1HF2370D7XM13/2/2023
101C1HF2370D7YM13/2/2023
101C1LN00529I3S12/2/2023
101C1LN00529I4S12/2/2023
101C1LN00529I5S12/2/2023
101C1LN00529IDS13/2/2023
101C1LN00529IES13/2/2023
101C1LN00529IKS13/2/2023
101C1LN00529ILS12/2/2023
101C1LN00529NXM12/2/2023
101C1LN00529NYM12/2/2023
101C1LN00529NZM12/2/2023
101C1LN00529O0M12/2/2023
101C1LN00529O1M12/2/2023
101C1LN00529OBM13/2/2023
101C1LN00529OCM13/2/2023
101C1LN00529ODM13/2/2023
101C1LN00529OEM13/2/2023
201C2HF32800T8S12/2/2023
201C2HF32800T9S12/2/2023
201C2HF32800TAS12/2/2023
201C2HF32800TBS12/2/2023
201C2HF32800TCS12/2/2023
201C2HF32800TDS12/2/2023
201C2HF32800TJS12/2/2023
201C2HF32800TKS12/2/2023
201C2HF32800TLS12/2/2023
201C2HF32800TMS12/2/2023
201C2HF32800U1P12/2/2023
201C2HF32800U2P12/2/2023
201C2HF32800U3P12/2/2023
201C2HF32800UPP12/2/2023
201C2HF32800UQP12/2/2023
201C2HF32800URP13/2/2023
201C2HF32800V6P13/2/2023
201C2HF32800V7P13/2/2023
201C2HF32800V8P12/2/2023
201C2HF32800V9P12/2/2023
201C2HF32800W5M12/2/2023
201C2HF32800W6M12/2/2023
201C2HF32800W7M12/2/2023
201C2HF32800W8M12/2/2023
201C2HF32800W9M12/2/2023
201C2HF32800WAM13/2/2023
201C2HF32800WBM13/2/2023
201C2HF32800WCM13/2/2023
201C2HF32800WDM13/2/2023
201C2HF32800WEM12/2/2023
201C2LN0032MQBP12/2/2023
201C2LN0032MQCP12/2/2023
201C2LN0032MQDP12/2/2023
201C2LN0032MQEP12/2/2023
201C2LN0032MQFP12/2/2023
201C2LN0032MQGP13/2/2023
201C2LN0032MQHP13/2/2023
201C2LN0032MQIP13/2/2023
201C2LN0032MQUP12/2/2023
201C2LN0032MQVP12/2/2023
201C2LN0032MT8M12/2/2023
201C2LN0032MT9M12/2/2023
201C2LN0032MTAM12/2/2023
201C2LN0032MTGM13/2/2023
201C2LN0032MTHM13/2/2023
201C2LN0032MTSM12/2/2023
201C2LN0032MTTM12/2/2023
201C2LN0032MTUM12/2/2023
301C3AB0031P44S12/2/2023
301C3AB0031P45S12/2/2023
301C3AB0031P46S12/2/2023
301C3AB0031P47S12/2/2023
301C3AB0031P48S13/2/2023
301C3AB0031P49S13/2/2023
301C3AB0031P4AS13/2/2023
301C3AB0031P4BS13/2/2023
301C3AB0031P4CS13/2/2023
301C3AB0031P4DS13/2/2023
301C3AB0031P4ES13/2/2023
301C3AB0031P4FS12/2/2023
301C3AB0031P4GS12/2/2023
301C3AB0031P4HS12/2/2023
301C3AB0031P4IS12/2/2023
301C3AB0031P5YP12/2/2023
301C3AB0031P5ZP12/2/2023
301C3AB003TP60P12/2/2023
301C3AB003TP61P12/2/2023
301C3AB0031P86P12/2/2023
301C3AB0031P87P12/2/2023
301C3AB0031P88P12/2/2023
301C3AB0031P8HM13/2/2023
301C3AB0031P8IM12/2/2023
301C3AB0031P8JM12/2/2023
301C3AB084233JP12/2/2023
301C3AB084233KP13/2/2023
301C3AB084233LP12/2/2023
301C3AB084233MP12/2/2023
301C3AB084235FM12/2/2023
301C3AB084235GM12/2/2023
301C3AB084235LM13/2/2023
301C3AB084235NM13/2/2023
301C3AB084235OM13/2/2023
301C3AB084235PM12/2/2023
301C3AB084235RM12/2/2023
301C3AB0950R66P12/2/2023
301C3AB0950R67P12/2/2023
301C3AB0950R6CP13/2/2023
301C3AB0950R6KP13/2/2023
301C3AB0950R6LP12/2/2023
301C3AB0950R6MP12/2/2023
301C3AB0950R6NP12/2/2023
301C3AB0950R6OP12/2/2023
301C3AB0950R6YP12/2/2023
301C3AB0950R6ZP12/2/2023

 

Thanks.

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago

    do not really get your point, just guess

    =AVERAGEX(SUMMARIZE('Table3','Table3'[Line],'Table3'[Shift]),CALCULATE(DISTINCTCOUNT(Table3[Part ID])))

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    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.