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:   Date State Product Damage% Sales Costs 10/01/2026 NSW Banana 0.1100 5000 2985 10/01/2026 QLD Banana 0.0900 3500 2035 ...
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) )
)

     

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.