Forum Discussion
Helper IIAverage in DAX
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.
Community SupportHi, 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.
Helper II
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.