Average based on column

[nuhasan]

Hi all,

I have below table. My goal are to show the target but I want to target be average when I filter 1 or 2 attribute.

Example 1: I filter process code 310 only and the target should be average of 94.3% & 95.3% = 94.8%

Example 2: I filter storage location PD5 only and the target should be averaeg of 55% & 74.3% =64.65%

Storage Location	Process Code	Segment	Machine	Target
EA	310	WASHI	310 EA Machine	94.30%
EB	310	WASHI	310 EB Machine	95.30%
PD2	612	EPM Paper	612 RB_PAP H.J Machine	98.50%
PD2	620	EPM Paper	620 RB_PAP Machine	98.50%
PD2	630	EPM Paper	630 BA_PAP Machine	98.50%
PD2	630	EPM Paper	630 BB_PAP Machine	98.50%
PD2	611	EPM PP/T	611 RA_PP H.J Machine	98.00%
PD2	630	EPM PP/T	630 BF_PP Machine	98.00%
PD2	620	EPM Leadframe	620 RA_LF Machine	92.40%
PD2	620	EPM Leadframe	620 RD_LF Machine	92.40%
PD3	610	EM DC/T	610 TB Machine	99.30%
PD3	610	WASHI	610 TB Machine	98.90%
PD4	640	WASHI	640 CH Machine	94.00%
PD4	640	WASHI	640 CD Machine	94.00%
PD4	640	WASHI	640 CK Machine	94.00%
PD4	640	WASHI	640 CG Machine	94.00%
PD4	640	WASHI	640 CC Machine	94.00%
PD4	640	EM DC/T	640 CB Machine	96.90%
PD4	640	EM DC/T	640 CC Machine	96.90%
PD4	640	DC/T	640 CB Machine	97.50%
PD4	640	DC/T	640 CC Machine	97.50%
PD4	640	EPM Polyester	640 CC Machine	97.50%
PD4	640	SPV	640 CB Machine	97.50%
PD4	640	TAPE	640 CC Machine	97.50%
PD4	640	TAPE	640 CD Machine	97.50%
PD4	400	EM DC/T	400 WH Machine	96.00%
PD4	400	DC/T	400 WH Machine	96.00%
PD4	400	WASHI	400 WK Machine	94.00%
PD5	620	EPM ELP	620 RC_ELP Machine	55.00%
PD5	650	EPM ELP	650 SA Machine	74.30%

Regards,

Nuha

[_AAndrade]

Hi @nuhasan,

The Sheets 1 table is filtering SAP table and not the oposite, so if you're using any filter from SAP table it will not filter target.
To solve the problem you could activate the both side relationship but it's not a good pratice and probably you could have problems in the future.

[_AAndrade · Accepted solution]

Hi @nuhasan,

I think I've probably misunderstood your question. You probably want to show the same table but with the target changing according to the filter you're using.
If that's the case, I present another solution on page 2 of my pbix.

[_AAndrade]

Hi @nuhasan,

Use this measure:

Simple AVG = AVERAGE(T_AllData[Target])

The final output:

[nuhasan]

Hi @_AAndrade ,

Actually I want the target reflect at another table. Now when I filter the location or month or other attribute, the target keep maintain 94.22% (which is the average of all target and not by filtering)

Regards,

Nuha