Forum Discussion

JulienETEX's avatar
JulienETEX
New Member
4 years ago
Solved

How to calculate an average based on several filters in ROW

Hi Community

 

I am getting my brain out of control ðŸ˜‘, I am searching for a solution to make a average calculation based on the same date and diffrent items with specific filters.

 

To explain the context, I got 3 columns in my database:

- Date of the measurement

- Type of control (BAS-PURETE and BAS-D90)

- Value of the control

 

DateCode SAP BIContrôleValeur
3/1/2022BAS-PURETEPureté Bascule84,95
3/1/2022BAS-D90D90 Bascule94,2
4/1/2022BAS-PURETEPureté Bascule88,49
4/1/2022BAS-D90D90 Bascule70,1
5/1/2022BAS-PURETEPureté Bascule87,96
5/1/2022BAS-D90D90 Bascule86,4

 

I want to make an average of the specific control "BAS-90" when AT THE SAME DATE (so same sample but different control) has a specific value.

 

For example:

IF "BAS-PURETE" > 86 at a specific date, I take into account (so in a filter) the value of the control "BAS-D90" for the same DATE to make the average with the other dates (if not I don't take the value)

 

It means that if "BAS-PURETE" > 86, in that example I only make the average of "BAS-D90" for dates 4/1/2022 and 5/1/2022 (NOT 3/1/2022 because BAS-PURETE < 86)

 

The problem is to work on ROWS for a sample of a same date. I tried AVERAGEX but works on columns, i want to work on ROWS but with a filter of a same date for both controls.

 

I don't know if it is clear...

Thanks for help !

 

Need Help
  • Icey's avatar
    Icey
    4 years ago

    Hi JulienETEX ,

     

    Please check if this is what you want:

    Avg =
VAR Date_ =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Code SAP BI] = "BAS-PURETE"
                && 'Table'[Valeur] > 86
        ),
        [Date]
    )
RETURN
    AVERAGEX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date]
                IN Date_
                && 'Table'[Code SAP BI] = "BAS-D90"
        ),
        [Valeur]
    )

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi JulienETEX ,

     

    Please check if this is what you want:

    Avg =
VAR Date_ =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Code SAP BI] = "BAS-PURETE"
                && 'Table'[Valeur] > 86
        ),
        [Date]
    )
RETURN
    AVERAGEX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date]
                IN Date_
                && 'Table'[Code SAP BI] = "BAS-D90"
        ),
        [Valeur]
    )

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • leilaetaati's avatar
    leilaetaati
    Frequent Visitor
    4 years ago

    Similar to above one just different way of writting 

    cal =

    var requiredDate=FILTER(All('TableData'),'TableData'[Cotrol]="BAS-PURETE    Pureté Bascule" && 'TableData'[Value]>86)
    Var requiredvalue=FILTER(All('TableData'),'TableData'[Cotrol]="BAS-D90  D90 Bascule" && 'TableData'[Date] in SELECTCOLUMNS( requiredDate,"Date",[Date]))
    return
    CALCULATE( AVERAGE([Value]),requiredvalue)
  • JulienETEX's avatar
    JulienETEX
    New Member
    4 years ago

    Thank you both!

     

    Your solutions runs prefectly 🙂 

    I will tru now to understand how it works !