divide values in the same column but have different descriptions

[Anonymous]

Can some see the problem with what i wrote?

COD % REDUCTION =

VAR Final_Effluent =  

SUMX(

    FILTER(

    'ELS Data 2019', 'ELS Data 2019'[Analyte] = "COD"  && 'ELS Data 2019'[sample points] = "Final Effluent" ),

            AVERAGE('ELS Data 2019'[2019 results]))

var Balancing_Tank =

SUMX(

    FILTER(

    'ELS Data 2019', 'ELS Data 2019'[Analyte] = "COD"  && 'ELS Data 2019'[sample points] = "Balance Tank" ),

            AVERAGE('ELS Data 2019'[2019 results]))

Return

    IF(

        NOT ISBLANK(Final_Effluent),

        ABS(DIVIDE( Final_Effluent-Balancing_Tank, Balancing_Tank)

    )

)

[parry2k]

@Anonymous are you getting wrong result or what is the issue? Can you share sample data and expected result?

[Anonymous]

basically im not gettin any numbers back
all the results should be between 80- 95 %

Description Sample Date 2019 results sample points Analyte

BT-PackagingPlant 020119	02/01/2019	1098	BT	COD
BT-PackagingPlant 090119	09/01/2019	346	BT	COD
BT-PackagingPlant 230119	23/01/2019	492	BT	COD
BT-PackagingPlant 160119	16/01/2019	702	BT	COD
Balancing Tank 240119	24/01/2019	630	Balancing Tank	COD
Balance Tank	08/01/2019	104	Balancing Tank	BOD
Balancing Tank 080119	08/01/2019	25.2	Balancing Tank	Total Nitrogen
Balancing Tank 170119	17/01/2019	1340	Balancing Tank	COD
Balancing Tank 180119	18/01/2019	850	Balancing Tank	COD
Balancing Tank 130119	13/01/2019	1190	Balancing Tank	COD
Balancing Tank 090119	09/01/2019	1190	Balancing Tank	COD
Balancing Tank 210119	21/01/2019	1140	Balancing Tank	COD
Balancing Tank 200119	20/01/2019	850	Balancing Tank	COD
Balancing Tank 080119	08/01/2019	1710	Balancing Tank	COD
Balancing Tank 220119	22/01/2019	830	Balancing Tank	COD
Balance Tank	22/01/2019	168	Balancing Tank	BOD
Balancing Tank 230119	23/01/2019	940	Balancing Tank	COD
Balancing Tank 100119	10/01/2019	1650	Balancing Tank	COD
Balancing Tank 220119	22/01/2019	22.9	Balancing Tank	Total Nitrogen
Balancing Tank 190119	19/01/2019	830	Balancing Tank	COD
Balancing Tank 110119	11/01/2019	1550	Balancing Tank	COD
Balancing Tank 040119	04/01/2019	440	Balancing Tank	COD
Balancing Tank 140119	14/01/2019	1230	Balancing Tank	COD
Balancing Tank 120119	12/01/2019	1310	Balancing Tank	COD
Balancing Tank 030119	03/01/2019	550	Balancing Tank	COD
Balancing Tank 150119	15/01/2019	24.4	Balancing Tank	Total Nitrogen
Balancing Tank 020119	02/01/2019	580	Balancing Tank	COD
Balancing Tank 150119	15/01/2019	1460	Balancing Tank	COD
Balancing Tank 150119	15/01/2019	15	Balancing Tank	BOD
Balancing Tank 160119	16/01/2019	1190	Balancing Tank	COD
Final Effluent 230119	23/01/2019	76	Final Effluent	COD
Final Effluent 240119	24/01/2019	29	Final Effluent	COD
Final Effluent 130119	13/01/2019	8	Final Effluent	COD
Final Effluent 220119	22/01/2019	17.6	Final Effluent	Total Nitrogen
Final Effluent 160119	16/01/2019	34	Final Effluent	COD
Final Effluent 170119	17/01/2019	40	Final Effluent	COD
Final Effluent 180119	18/01/2019	38	Final Effluent	COD
Final Effluent 150119	15/01/2019	9.6	Final Effluent	Total Nitrogen
Final Effluent 150119	15/01/2019	44	Final Effluent	COD
Final Effluent 190119	19/01/2019	27	Final Effluent	COD
Final Effluent 210119	21/01/2019	31	Final Effluent	COD
Final Effluent 200119	20/01/2019	28	Final Effluent	COD
Final Effluent 220119	22/01/2019	90	Final Effluent	COD
Final Effluent 080119	08/01/2019	51	Final Effluent	COD
Final Effluent 090119	09/01/2019	76	Final Effluent	COD
Final Effluent 100119	10/01/2019	66	Final Effluent	COD
Final Effluent 080119	08/01/2019	12.9	Final Effluent	Total Nitrogen
Final Effluent 040119	04/01/2019	8	Final Effluent	COD
Final Effluent 301218	30/12/2018	51	Final Effluent	COD
Final Effluent 120119	12/01/2019	42	Final Effluent	COD
Final Effluent 020119	02/01/2019	53	Final Effluent	COD
Final Effluent 110119	11/01/2019	71	Final Effluent	COD
Final Effluent 140119	14/01/2019	19	Final Effluent	COD
Final Effluent 030119	03/01/2019	42	Final Effluent	COD
Quench Liquor 230119	23/01/2019	19.4	Quench	Total Nitrogen
Quench Liquor 090119	09/01/2019	14.6	Quench	Total Nitrogen
Quench Liquor 160119	16/01/2019	15.5	Quench	Total Nitrogen
Clear Effluent 080119	08/01/2019	8.8	clear effluent	Total Nitrogen
Clear Effluent 220119	22/01/2019	15.8	clear effluent	Total Nitrogen
Clear Effluent 080119	08/01/2019	46	clear effluent	COD
Clear Effluent 150119	15/01/2019	49	clear effluent	BOD
Clear Effluent	22/01/2019	5	clear effluent	BOD
Clear Effluent 150119	15/01/2019	33	clear effluent	COD
Clear Effluent 150119	15/01/2019	7.3	clear effluent	Total Nitrogen
Clear Effluent	08/01/2019	47	clear effluent	BOD
Clear Effluent 220119	22/01/2019	106	clear effluent	COD
Surface Water 020119	02/01/2019	8	surface water	COD
Surface Water 090119	09/01/2019	8	surface water	COD
Surface Water 160119	16/01/2019	14	surface water	COD
Surface Water 230119	23/01/2019	50	surface water	COD

[parry2k]

@Anonymous can you share data thru excel, share thru one drive/google drive.

[Anonymous]

https://drive.google.com/file/d/1ufPk06-8YLIvDRpym99rM9ap07iSkX9l/view?usp=sharing

---

@parry2k wrote:

@Anonymouscan you share data thru excel, share thru one drive/google drive.

---

[parry2k · Accepted solution]

@Anonymous one issue i found tht you used value "Balance Tank" where as in your data it is "Balancing Tank" and your measure was returning any value, here is the measure

COD % REDUCTION = 

VAR Final_Effluent =   
CALCULATE( AVERAGE( Sheet1[2019 results] ), Sheet1[Analyte] = "COD", Sheet1[sample points] = "Final Effluent" )

var Balancing_Tank = 
CALCULATE( AVERAGE( Sheet1[2019 results] ), Sheet1[Analyte] = "COD", Sheet1[sample points] = "Balancing Tank" )

Return
    IF(
        NOT ISBLANK(Final_Effluent),
        ABS(DIVIDE( Final_Effluent-Balancing_Tank, Balancing_Tank)
    ) 
)