Distinct Sum based on another column with additional Fİlters

[Anonymous]

Hi We are having problem with this function that we wrote.

Min value, hammadde bazında =

VAR mySymbol = 'VA05-Açık Sipariş'[Hammadde / Çap]

VAR duedate=TODAY()

VAR myFilter = FILTER(

            'VA05-Açık Sipariş',

            'VA05-Açık Sipariş'[Hammadde / Çap] = mySymbol && 'VA05-Açık Sipariş'[30 Gün önceki Tarih Bilgisi]<=duedate && 'VA05-Açık Sipariş'[mın value]<0       

            )

RETURN

    SUMX(

        myFilter  ,

        CALCULATE(AVERAGE('VA05-Açık Sipariş'[mın value]),ALLEXCEPT('VA05-Açık Sipariş','VA05-Açık Sipariş'[Malzeme])))

Our purpose here is to calculate our min values with according to distinct Values from Malzeme Column.We have additinional filters that we need for our calculation.the result that we have from this function -18.21 but our actual results is -11.22 from our sample data.You can see our sample data as follows;

[tamerj1 · Accepted solution]

Hi @Anonymous 
Pleasr try

Min value, hammadde bazında =
VAR duedate =
    TODAY ()
VAR CurrentSymbolTable =
    CALCULATETABLE (
        'VA05-Açık Sipariş',
        ALLEXCEPT ( 'VA05-Açık Sipariş', 'VA05-Açık Sipariş'[Hammadde / Çap] )
    )
VAR myFilter =
    FILTER (
        CurrentSymbolTable,
        'VA05-Açık Sipariş'[30 Gün önceki Tarih Bilgisi] <= duedate
            && 'VA05-Açık Sipariş'[mın value] < 0
    )
VAR mySummary =
    SUMMARIZE (
        myFilter,
        'VA05-Açık Sipariş'[Malzeme],
        "@AverageMinValue", AVERAGE ( 'VA05-Açık Sipariş'[mın value] )
    )
RETURN
    SUMX ( mySummary, [@AverageMinValue] )

[Anonymous]

Thank you.It worked perfectly.Thank you for your time and work.

[Anonymous]

You can access our Sample Data from here as well;

Malzeme	30 Gün önceki Tarih Bilgisi	mın value	Min value, hammadde bazında
4000997	20 Mar 2022	-0,20679	-18,2091
4000997	11 Ara 2022	-0,20679	-18,2091
4001005	11 Eki 2022	0,002378	-18,2091
4000997	20 Mar 2020	-0,20679	-18,2091
4000970	2 May 2022	-0,0002	-18,2091
4000966	26 Ağu 2022	-0,94448	-18,2091
4000973	2 May 2022	0,715661	-18,2091
4000973	26 Ağu 2022	0,715661	-18,2091
4000966	2 May 2022	-0,94448	-18,2091
4000970	26 Ağu 2022	-0,0002	-18,2091
4044492	2 Oca 2023		-18,2091
4063452	17 Kas 2022	-0,46031	-18,2091
4022388	7 Haz 2022	-2,39412	-18,2091
4021106	7 Haz 2022	-1,05643	-18,2091
4021533	7 Haz 2022	-2,73499	-18,2091
4021214	7 Haz 2022	-0,53798	-18,2091
4066874	30 Kas 2022	-0,01521	-18,2091
4064591	18 Eki 2022	-2,82837	-18,2091
4064591	29 Oca 2023	-2,82837	-18,2091
4064591	29 Oca 2023	-2,82837	-18,2091
4064591	19 Ara 2022	-2,82837	-18,2091
4064591	18 Eki 2022	-2,82837	-18,2091
4000127	30 Ara 2022	48,13617	-18,2091
4000127	6 Ara 2022	48,13617	-18,2091
4000127	11 Kas 2022	48,13617	-18,2091
4000127	11 Kas 2022	48,13617	-18,2091
4000127	21 Ara 2022	48,13617	-18,2091
4064591	23 Eki 2022	-2,82837	-18,2091
4064591	19 Ara 2022	-2,82837	-18,2091
4064593	19 Ara 2022	0,20919	-18,2091
4064593	29 Oca 2023	0,20919	-18,2091
4064593	18 Eki 2022	0,20919	-18,2091
4000137	21 Ara 2022	0,21639	-18,2091
4000137	11 Kas 2022	0,21639	-18,2091
4064593	19 Ara 2022	0,20919	-18,2091
4064740	19 Ara 2022		-18,2091
4064740	19 Ara 2022		-18,2091
4066874	17 Kas 2022	-0,01521	-18,2091
4000127	6 Ara 2022	48,13617	-18,2091
4000127	30 Kas 2022	48,13617	-18,2091
4000127	6 Ara 2022	48,13617	-18,2091
4000137	6 Ara 2022	0,21639	-18,2091
4000127	17 Kas 2022	48,13617	-18,2091