How to perform a calculation with advanced filters?

[MarcoMM]

I have a spreadsheet with the following fields:

The “Valor do m2” field is calculated as follows:

The following measures were then created:

My ultimate goal is to calculate a new average based on the final result of the measurements between “IntervaloMin” and “IntervaloMax”, and I tried the formula below, but I was not successful.

Could someone please help me with a suggestion? And example?

[v-yanjiang-msft · Accepted solution]

Hi @MarcoMM ,

According to your description, I create a sample.

It get the incorrect result by your formula.

Here's my solution, modify the formula to:

ValorMedF =
CALCULATE (
    AVERAGE ( Base[Valor do m2] ),
    FILTER (
        ALL ( 'Base' ),
        'Base'[Valor do m2]
            >= AVERAGE ( Base[Valor do m2] )
                - AVERAGE ( Base[Valor do m2] ) * 0.3
            && 'Base'[Valor do m2]
                <= AVERAGE ( Base[Valor do m2] )
                    + AVERAGE ( Base[Valor do m2] ) * 0.3
    )
)

Get the correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[MarcoMM]

Thank for your support @v-yanjiang-msft 

I just made an adjustment to the quey

ValorMedF =
CALCULATE (
    AVERAGE ( Base[Valor do m2] ),
    FILTER (Base,
        'Base'[Valor do m2]
            >= AVERAGE ( Base[Valor do m2] )
                - AVERAGE ( Base[Valor do m2] ) * 0.3
            && 'Base'[Valor do m2]
                <= AVERAGE ( Base[Valor do m2] )
                    + AVERAGE ( Base[Valor do m2] ) * 0.3
    )
)

 So it work exactly as expected,

Thank you very much!

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523