Distinct Sum based on Another Column

[Boe]

Hi,We are trying to Sum our min value column based on disticnt values from Malzeme Column.But we have additional filters that we need for our calculation.

Our code is goes as follows;

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

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

And using this function according to  our sample data we have the result of -18,209.However the real result is -11.18

I hope you can help.

Our Sample Data as follows;

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

[v-shex-msft · Accepted solution]

HI @Boe,

It sounds like a common multiple aggregation calculate on measures formula(sum min value abased on category group), you can try to use the following measure formula if it suitable for your requirement:

Min value, hammadde bazında =
VAR mySymbol = 'VA05-Açık Sipariş'[Hammadde / Çap]
VAR summary =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'VA05-Açık Sipariş' ),
            'VA05-Açık Sipariş'[Hammadde / Çap] = mySymbol
                && 'VA05-Açık Sipariş'[30 Gün önceki Tarih Bilgisi] <= TODAY ()
                && 'VA05-Açık Sipariş'[mın value] < 0
        ),
        [Malzeme],
        "_Min", MIN ( 'VA05-Açık Sipariş'[mın value] )
    )
RETURN
    SUMX ( summary, [_Min] )

Regards,

Xiaoxin Sheng