How to calculate an average based on several filters in ROW

[JulienETEX]

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

Date	Code SAP BI	Contrôle	Valeur
3/1/2022	BAS-PURETE	Pureté Bascule	84,95
3/1/2022	BAS-D90	D90 Bascule	94,2
4/1/2022	BAS-PURETE	Pureté Bascule	88,49
4/1/2022	BAS-D90	D90 Bascule	70,1
5/1/2022	BAS-PURETE	Pureté Bascule	87,96
5/1/2022	BAS-D90	D90 Bascule	86,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 !

[JulienETEX]

Thank you both!

Your solutions runs prefectly 🙂 

I will tru now to understand how it works ! 

[leilaetaati]

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)

[Icey · Accepted solution]

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.

[JulienETEX]

Not possible? 😞