Forum Discussion
Distinct Sum based on another column with additional Fİlters
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;
Hi Anonymous
Pleasr tryMin 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] )
3 Replies
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 - tamerj1
Community Champion
Hi Anonymous
Pleasr tryMin 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] )