Skip to main content
cancel
Showing results for 
Search instead 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

Reply
MarcoMM
New Member

How to perform a calculation with advanced filters?

I have a spreadsheet with the following fields:

MarcoMM_0-1664037655375.png

 

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

MarcoMM_1-1664037655378.png

 

 

The following measures were then created:

MarcoMM_2-1664037655379.png

MarcoMM_3-1664037655380.png

MarcoMM_4-1664037655380.png

 

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.

MarcoMM_5-1664037655381.png

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

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @MarcoMM ,

According to your description, I create a sample.

vkalyjmsft_0-1664157980504.png

It get the incorrect result by your formula.

vkalyjmsft_1-1664158074989.png

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.

vkalyjmsft_2-1664158298467.png

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.

View solution in original post

3 REPLIES 3
v-yanjiang-msft
Community Support
Community Support

Hi @MarcoMM ,

According to your description, I create a sample.

vkalyjmsft_0-1664157980504.png

It get the incorrect result by your formula.

vkalyjmsft_1-1664158074989.png

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.

vkalyjmsft_2-1664158298467.png

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.

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
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors