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