Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Boe
Frequent Visitor

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;
sample data.png
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Boe 
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] )

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hi @Boe 
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] )
Boe
Frequent Visitor

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

Boe
Frequent Visitor

You can access our Sample Data from here as well;

Malzeme30 Gün önceki Tarih Bilgisimın valueMin value, hammadde bazında
400099720 Mar 2022-0,20679-18,2091
400099711 Ara 2022-0,20679-18,2091
400100511 Eki 20220,002378-18,2091
400099720 Mar 2020-0,20679-18,2091
40009702 May 2022-0,0002-18,2091
400096626 Ağu 2022-0,94448-18,2091
40009732 May 20220,715661-18,2091
400097326 Ağu 20220,715661-18,2091
40009662 May 2022-0,94448-18,2091
400097026 Ağu 2022-0,0002-18,2091
40444922 Oca 2023 -18,2091
406345217 Kas 2022-0,46031-18,2091
40223887 Haz 2022-2,39412-18,2091
40211067 Haz 2022-1,05643-18,2091
40215337 Haz 2022-2,73499-18,2091
40212147 Haz 2022-0,53798-18,2091
406687430 Kas 2022-0,01521-18,2091
406459118 Eki 2022-2,82837-18,2091
406459129 Oca 2023-2,82837-18,2091
406459129 Oca 2023-2,82837-18,2091
406459119 Ara 2022-2,82837-18,2091
406459118 Eki 2022-2,82837-18,2091
400012730 Ara 202248,13617-18,2091
40001276 Ara 202248,13617-18,2091
400012711 Kas 202248,13617-18,2091
400012711 Kas 202248,13617-18,2091
400012721 Ara 202248,13617-18,2091
406459123 Eki 2022-2,82837-18,2091
406459119 Ara 2022-2,82837-18,2091
406459319 Ara 20220,20919-18,2091
406459329 Oca 20230,20919-18,2091
406459318 Eki 20220,20919-18,2091
400013721 Ara 20220,21639-18,2091
400013711 Kas 20220,21639-18,2091
406459319 Ara 20220,20919-18,2091
406474019 Ara 2022 -18,2091
406474019 Ara 2022 -18,2091
406687417 Kas 2022-0,01521-18,2091
40001276 Ara 202248,13617-18,2091
400012730 Kas 202248,13617-18,2091
40001276 Ara 202248,13617-18,2091
40001376 Ara 20220,21639-18,2091
400012717 Kas 202248,13617-18,2091

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors