PowerPivot Filter Function

Question

In PowerPivot Excel 2016 I write a formula to summarize year to date sales using filter function as below:

SalesYTD:=CALCULATE (
[Net Sales], 
FILTER (
    ALL ( Sales), 
    'sales'[Year] = MAX ( 'Sales'[Year]  )
        && 'Sales'[Date] <= MAX ( 'Sales'[Date] )
  )
)

And it's work perfectly, now in my data I have a field called "Channel" which I want to filter it in my pivot table but it won't works! Does anybody knows how should I fix this formula?!

Thanks in advance...

Do you want to filter the Channel directly from the formula or do you want to filter via Pivot Table filters? What do you mean with "it won't work!"? Be more specific if you want an answer to your question. — alejandro zuleta
Hi, i want to filter via Pivot Table filters, right now if i change my filter, my value does not change! — Ahmad AB

alejandro zuleta alejandro zuleta · Accepted Answer · 2017-01-20T15:32:12

Try:

SalesYTD:=CALCULATE (
[Net Sales], 
FILTER (
    ALLEXCEPT ( 'Sales', 'Sales'[Channel] ), 
    'sales'[Year] = MAX ( 'Sales'[Year]  )
        && 'Sales'[Date] <= MAX ( 'Sales'[Date] )
  )
)

ALLEXCEPT removes all context filters in the table except filters that have been applied to the specified columns, in this case [Channel] column.

Let me know if this helps.

PowerPivot Filter Function

1 Answers