cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## How to perform a calculation with advanced filters?

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.

1 ACCEPTED SOLUTION
Community Support

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.

3 REPLIES 3
Community Support

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.

New Member

``````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!

Super User

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.