Average in DAX

[Tan_LC]

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

[v-zhangti]

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]

@v-zhangti 

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.

[Tan_LC]

@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.

[ERD]

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

[Tan_LC]

@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. 

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.

[v-zhangti · Accepted solution]

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.

[wdx223_Daniel]

do not really get your point, just guess

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