cancel
Showing results for
Did you mean:
New Member

## 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

 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 !

1 ACCEPTED SOLUTION
Community Support

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 4
New Member

Thank you both!

I will tru now to understand how it works !

Frequent Visitor

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)
Community Support

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.

New Member

Not possible? 😞