cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JulienETEX
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

 

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 !

 

1 ACCEPTED SOLUTION
Icey
Community Support
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]
    )

Icey_0-1649038461438.png

 

Best Regards,

Icey

 

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

View solution in original post

4 REPLIES 4
JulienETEX
New Member

Thank you both!

 

Your solutions runs prefectly 🙂 

I will tru now to understand how it works ! 

leilaetaati
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)
2022-04-04_15h36_01.png
Icey
Community Support
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]
    )

Icey_0-1649038461438.png

 

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

Not possible? 😞 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors