Forum Discussion
Helper IIAverage 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 turns to be abnormal which giving me a single digit number instead of 4 digits numeric (as shown in "Average in DAX").
Please help.
| Line | 1: S | 2: P | 3: M | Average in Excel | Average in DAX |
| C1 | 2,012 | 2,347 | 2,261 | 2,207 | 7.13 |
| C2 | 1,796 | 1,939 | 1,934 | 1,890 | 6.76 |
| C3 | 2,149 | 2,242 | 2,216 | 2,202 | 8.69 |
Thank you.
Regards,
Lee Cheng
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.
7 Replies
Tan_LCI 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.
Line S P M Ave in Excel C1 24 17 25 22 C2 10 20 18 16 C3 15 21 10 15 Machine
No.Line Part No. Part ID Shift Date 101 C1 HF135 32D5 S 12/2/2023 101 C1 HF135 32D6 S 12/2/2023 101 C1 HF135 32D7 S 12/2/2023 101 C1 HF135 32D8 S 12/2/2023 101 C1 HF135 32DH S 13/2/2023 101 C1 HF135 32DI S 13/2/2023 101 C1 HF135 32DJ S 13/2/2023 101 C1 HF135 32DK S 13/2/2023 101 C1 HF135 32EE P 12/2/2023 101 C1 HF135 32EF P 12/2/2023 101 C1 HF135 32EM P 13/2/2023 101 C1 HF135 32EO P 13/2/2023 101 C1 HF135 32ES P 12/2/2023 101 C1 HF135 32ET P 12/2/2023 101 C1 HF135 32EU P 12/2/2023 101 C1 HF135 32HB M 12/2/2023 101 C1 HF135 32HC M 12/2/2023 101 C1 HF135 32HD M 12/2/2023 101 C1 HF135 32HE M 12/2/2023 101 C1 HF135 32HL M 13/2/2023 101 C1 HF135 32HM M 13/2/2023 101 C1 HF135 32HN M 13/2/2023 101 C1 HF237 0D0W S 12/2/2023 101 C1 HF237 0D0X S 12/2/2023 101 C1 HF237 0D0Y S 12/2/2023 101 C1 HF237 0D0Z S 13/2/2023 101 C1 HF237 0D10 S 13/2/2023 101 C1 HF237 0D11 S 13/2/2023 101 C1 HF237 0D12 S 13/2/2023 101 C1 HF237 0D13 S 13/2/2023 101 C1 HF237 0D1B S 12/2/2023 101 C1 HF237 0D1P P 12/2/2023 101 C1 HF237 0D1Q P 12/2/2023 101 C1 HF237 0D1R P 12/2/2023 101 C1 HF237 0D1S P 12/2/2023 101 C1 HF237 0D1T P 12/2/2023 101 C1 HF237 0D1U P 12/2/2023 101 C1 HF237 0D20 P 13/2/2023 101 C1 HF237 0D21 P 13/2/2023 101 C1 HF237 0D22 P 13/2/2023 101 C1 HF237 0D23 P 13/2/2023 101 C1 HF237 0D7J M 12/2/2023 101 C1 HF237 0D7M M 12/2/2023 101 C1 HF237 0D7N M 12/2/2023 101 C1 HF237 0D7O M 12/2/2023 101 C1 HF237 0D7P M 12/2/2023 101 C1 HF237 0D7Q M 13/2/2023 101 C1 HF237 0D7R M 13/2/2023 101 C1 HF237 0D7X M 13/2/2023 101 C1 HF237 0D7Y M 13/2/2023 101 C1 LN005 29I3 S 12/2/2023 101 C1 LN005 29I4 S 12/2/2023 101 C1 LN005 29I5 S 12/2/2023 101 C1 LN005 29ID S 13/2/2023 101 C1 LN005 29IE S 13/2/2023 101 C1 LN005 29IK S 13/2/2023 101 C1 LN005 29IL S 12/2/2023 101 C1 LN005 29NX M 12/2/2023 101 C1 LN005 29NY M 12/2/2023 101 C1 LN005 29NZ M 12/2/2023 101 C1 LN005 29O0 M 12/2/2023 101 C1 LN005 29O1 M 12/2/2023 101 C1 LN005 29OB M 13/2/2023 101 C1 LN005 29OC M 13/2/2023 101 C1 LN005 29OD M 13/2/2023 101 C1 LN005 29OE M 13/2/2023 201 C2 HF328 00T8 S 12/2/2023 201 C2 HF328 00T9 S 12/2/2023 201 C2 HF328 00TA S 12/2/2023 201 C2 HF328 00TB S 12/2/2023 201 C2 HF328 00TC S 12/2/2023 201 C2 HF328 00TD S 12/2/2023 201 C2 HF328 00TJ S 12/2/2023 201 C2 HF328 00TK S 12/2/2023 201 C2 HF328 00TL S 12/2/2023 201 C2 HF328 00TM S 12/2/2023 201 C2 HF328 00U1 P 12/2/2023 201 C2 HF328 00U2 P 12/2/2023 201 C2 HF328 00U3 P 12/2/2023 201 C2 HF328 00UP P 12/2/2023 201 C2 HF328 00UQ P 12/2/2023 201 C2 HF328 00UR P 13/2/2023 201 C2 HF328 00V6 P 13/2/2023 201 C2 HF328 00V7 P 13/2/2023 201 C2 HF328 00V8 P 12/2/2023 201 C2 HF328 00V9 P 12/2/2023 201 C2 HF328 00W5 M 12/2/2023 201 C2 HF328 00W6 M 12/2/2023 201 C2 HF328 00W7 M 12/2/2023 201 C2 HF328 00W8 M 12/2/2023 201 C2 HF328 00W9 M 12/2/2023 201 C2 HF328 00WA M 13/2/2023 201 C2 HF328 00WB M 13/2/2023 201 C2 HF328 00WC M 13/2/2023 201 C2 HF328 00WD M 13/2/2023 201 C2 HF328 00WE M 12/2/2023 201 C2 LN003 2MQB P 12/2/2023 201 C2 LN003 2MQC P 12/2/2023 201 C2 LN003 2MQD P 12/2/2023 201 C2 LN003 2MQE P 12/2/2023 201 C2 LN003 2MQF P 12/2/2023 201 C2 LN003 2MQG P 13/2/2023 201 C2 LN003 2MQH P 13/2/2023 201 C2 LN003 2MQI P 13/2/2023 201 C2 LN003 2MQU P 12/2/2023 201 C2 LN003 2MQV P 12/2/2023 201 C2 LN003 2MT8 M 12/2/2023 201 C2 LN003 2MT9 M 12/2/2023 201 C2 LN003 2MTA M 12/2/2023 201 C2 LN003 2MTG M 13/2/2023 201 C2 LN003 2MTH M 13/2/2023 201 C2 LN003 2MTS M 12/2/2023 201 C2 LN003 2MTT M 12/2/2023 201 C2 LN003 2MTU M 12/2/2023 301 C3 AB003 1P44 S 12/2/2023 301 C3 AB003 1P45 S 12/2/2023 301 C3 AB003 1P46 S 12/2/2023 301 C3 AB003 1P47 S 12/2/2023 301 C3 AB003 1P48 S 13/2/2023 301 C3 AB003 1P49 S 13/2/2023 301 C3 AB003 1P4A S 13/2/2023 301 C3 AB003 1P4B S 13/2/2023 301 C3 AB003 1P4C S 13/2/2023 301 C3 AB003 1P4D S 13/2/2023 301 C3 AB003 1P4E S 13/2/2023 301 C3 AB003 1P4F S 12/2/2023 301 C3 AB003 1P4G S 12/2/2023 301 C3 AB003 1P4H S 12/2/2023 301 C3 AB003 1P4I S 12/2/2023 301 C3 AB003 1P5Y P 12/2/2023 301 C3 AB003 1P5Z P 12/2/2023 301 C3 AB003 TP60 P 12/2/2023 301 C3 AB003 TP61 P 12/2/2023 301 C3 AB003 1P86 P 12/2/2023 301 C3 AB003 1P87 P 12/2/2023 301 C3 AB003 1P88 P 12/2/2023 301 C3 AB003 1P8H M 13/2/2023 301 C3 AB003 1P8I M 12/2/2023 301 C3 AB003 1P8J M 12/2/2023 301 C3 AB084 233J P 12/2/2023 301 C3 AB084 233K P 13/2/2023 301 C3 AB084 233L P 12/2/2023 301 C3 AB084 233M P 12/2/2023 301 C3 AB084 235F M 12/2/2023 301 C3 AB084 235G M 12/2/2023 301 C3 AB084 235L M 13/2/2023 301 C3 AB084 235N M 13/2/2023 301 C3 AB084 235O M 13/2/2023 301 C3 AB084 235P M 12/2/2023 301 C3 AB084 235R M 12/2/2023 301 C3 AB095 0R66 P 12/2/2023 301 C3 AB095 0R67 P 12/2/2023 301 C3 AB095 0R6C P 13/2/2023 301 C3 AB095 0R6K P 13/2/2023 301 C3 AB095 0R6L P 12/2/2023 301 C3 AB095 0R6M P 12/2/2023 301 C3 AB095 0R6N P 12/2/2023 301 C3 AB095 0R6O P 12/2/2023 301 C3 AB095 0R6Y P 12/2/2023 301 C3 AB095 0R6Z P 12/2/2023 Thanks.
- wdx223_Daniel
Community Champion
do not really get your point, just guess
=AVERAGEX(SUMMARIZE('Table3','Table3'[Line],'Table3'[Shift]),CALCULATE(DISTINCTCOUNT(Table3[Part ID])))
v-zhangtiCommunity Support
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
In below data, would appreciate if you can advise on the formula to get the Ave using DAX for Line.
I've tried on your earlier formula, if there are new columns such as Date, Machine No and & Part No added, the Ave result becomes inaccurate.
Date Line Shift Machine No. Part No. Output 12/2/2023 C1 M 101 HF135 4 12/2/2023 C1 M 101 HF237 5 12/2/2023 C1 M 101 LN005 5 12/2/2023 C1 P 101 HF135 5 12/2/2023 C1 P 101 HF237 6 12/2/2023 C1 S 101 HF135 4 12/2/2023 C1 S 101 HF237 4 12/2/2023 C1 S 101 LN005 4 13/2/2023 C1 M 101 HF135 3 13/2/2023 C1 M 101 HF237 4 13/2/2023 C1 M 101 LN005 4 13/2/2023 C1 P 101 HF135 2 13/2/2023 C1 P 101 HF237 4 13/2/2023 C1 S 101 LN005 3 13/2/2023 C1 S 101 HF135 4 13/2/2023 C1 S 101 HF237 5 12/2/2023 C2 M 201 HF328 6 12/2/2023 C2 M 201 LN003 6 12/2/2023 C2 P 201 HF328 7 12/2/2023 C2 P 201 LN003 7 12/2/2023 C2 S 201 HF328 10 13/2/2023 C2 M 201 LN003 2 13/2/2023 C2 M 201 HF328 4 13/2/2023 C2 P 201 HF328 3 13/2/2023 C2 P 201 LN003 3 12/2/2023 C3 M 301 AB003 2 12/2/2023 C3 M 301 AB084 4 12/2/2023 C3 P 301 AB084 3 12/2/2023 C3 P 301 AB003 7 12/2/2023 C3 P 301 AB095 8 12/2/2023 C3 S 301 AB003 8 13/2/2023 C3 M 301 AB003 1 13/2/2023 C3 M 301 AB084 3 13/2/2023 C3 P 301 AB084 1 13/2/2023 C3 P 301 AB095 2 13/2/2023 C3 S 301 AB003 7 Thanks.
