Forum Discussion

powerlight1's avatar
powerlight1
Helper I
6 months ago
Solved

Apply Filter before Calculating Average

I have the following product-state-date sales data:

 

DateStateProductDamage%SalesCosts
10/01/2026NSWBanana0.110050002985
10/01/2026QLDBanana0.090035002035
10/01/2026VICBanana0.150038002157
10/01/2026NSWApple0.057044003244
10/01/2026QLDApple0.210029801859
10/01/2026VICApple0.049032152470
11/01/2026NSWBanana0.104551902835
11/01/2026QLDBanana0.085536331933
11/01/2026VICBanana0.142539442049
11/01/2026NSWApple0.054245673081
11/01/2026QLDApple0.199530931766
11/01/2026VICApple0.046633372346
12/01/2026NSWBanana0.103548553029
12/01/2026QLDBanana0.084733992065
12/01/2026VICBanana0.141236902189
12/01/2026NSWApple0.053642723292
12/01/2026QLDApple0.197628941886
12/01/2026VICApple0.046131222507

 

I have the PowerBI summarised table for all 3 day's results (note: we just want to summarise by Product-State and not date):

 

What I want to do is create a filter measure to allow me to filter out those product-state-date where the Damage% is greater than a certain threshold (eg.11%).  I created the following measure:

I then place "Filter_DamageThreshold" in the Filter section of the above summary table and select value of "1"

The result is that it is only filtering out the following rows:

 

 

 

What I really want to do is to filter out those product-state-date with Damage% greater than or equal to 11% first and then calculate the summary table.  Below rows in yellow is what I want to filter out or exclude from the dataset first:

 

...and then calculate the summary table therefore giving the following results:

 

 

 

Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    6 months ago

    Your visual-level filter measure is evaluated at the current visual grain (Product-State), not at the underlying Product-State-Date row level, so it won’t exclude date rows first. Use the threshold condition inside each aggregation measure instead:

    Threshold % = 0.11
    Sales (Filtered) =
VAR T = [Threshold %]
RETURN
CALCULATE(
    SUM('Fact'[Sales]),
    KEEPFILTERS( FILTER('Fact', 'Fact'[Damage%] < T) )
)
    Costs (Filtered) =
VAR T = [Threshold %]
RETURN
CALCULATE(
    SUM('Fact'[Costs]),
    KEEPFILTERS( FILTER('Fact', 'Fact'[Damage%] < T) )
)
    Damage % (Filtered) =
VAR T = [Threshold %]
RETURN
CALCULATE(
    AVERAGE('Fact'[Damage%]),
    KEEPFILTERS( FILTER('Fact', 'Fact'[Damage%] < T) )
)

     

5 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    6 months ago

    The solution provided by cengizhanarslan will work 100%. Another method to solving this is using the 'X' functions. In this case SUMX and AVERAGEX, with a filtered table input. As an example...

    Average Damage (Excess damage removed) = 
AVERAGEX(
    FILTER('Table', 'Table'[Damage%] < 0.11),
    'Table'[Damage%]
)

    As in the other solution you can create a measure or parameter for the threshold value and reference to it in this measure. I hardcoded it for simplicity.
    Cheers.

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    6 months ago

    Your visual-level filter measure is evaluated at the current visual grain (Product-State), not at the underlying Product-State-Date row level, so it won’t exclude date rows first. Use the threshold condition inside each aggregation measure instead:

    Threshold % = 0.11
    Sales (Filtered) =
VAR T = [Threshold %]
RETURN
CALCULATE(
    SUM('Fact'[Sales]),
    KEEPFILTERS( FILTER('Fact', 'Fact'[Damage%] < T) )
)
    Costs (Filtered) =
VAR T = [Threshold %]
RETURN
CALCULATE(
    SUM('Fact'[Costs]),
    KEEPFILTERS( FILTER('Fact', 'Fact'[Damage%] < T) )
)
    Damage % (Filtered) =
VAR T = [Threshold %]
RETURN
CALCULATE(
    AVERAGE('Fact'[Damage%]),
    KEEPFILTERS( FILTER('Fact', 'Fact'[Damage%] < T) )
)

     

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    6 months ago

    Thankyou, cengizhanarslan, jgeddes and Ashish_Mathur for your responses.

    Hi powerlight1,

    We appreciate your inquiry through the Microsoft Fabric Community Forum.

    We would like to inquire whether have you got the chance to check the solutions provided by cengizhanarslan, jgeddes and Ashish_Mathur  to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

    Thank you.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    6 months ago

    Hi powerlight1,

    We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

    Thank you.