Forum Discussion
Exclude values from Average calculation
Hi,
I have a matrix table created with with average sales for each department. We recently added a new department that did not have any sales for 2 months before generating sales. Due to this, the average for that particular branch is wrongly reported. I will like to exclude the months without sales from the average just for that department only in order to get the right average begining from the month sales started generating.
Any idea on how i can calculate average for just one department without the 0 sales months being included?
Thank you
You're right. This worked for me, then:
SalesFiltered =
VAR _Exclude =
CALCULATE (
SUM(dailyKPI[Sales]),
KEEPFILTERS ( dailyKPI[Warehouse] = "60"),
KEEPFILTERS ( dailyKPI[Month - Year] IN {"September 2020","October 2020", "November 2020", "December 2020", "August 2021","September 2021","October 2021"} )
)
RETURN
SUM(dailyKPI[Sales]) - _Exclude--NOTE: if possible, apply the filters to the dimension table (not the fact table)
5 Replies
Is this a specific, non-repeating scenario?
If so, you may add to CALCULATE the modifier KEEPFILTERS:
=CALCULATE(
{Formula},
KEEPFILTERS(NOT(AND('Sales'[Department]="Department X", 'Calendar'[Year-Month] IN {2021-02,2021-03})
)
Hi
I tried this measure and it didnt work. I get an error.
SalesFiltered = CALCULATE(SUM(dailyKPI[Sales]),KEEPFILTERS(NOT(AND(dailyKPI[Warehouse] = "60", dailyKPI[Month - Year] IN {"September 2020","October 2020", "November 2020", "December 2020", "August 2021","September 2021","October 2021"}))))You're right. This worked for me, then:
SalesFiltered =
VAR _Exclude =
CALCULATE (
SUM(dailyKPI[Sales]),
KEEPFILTERS ( dailyKPI[Warehouse] = "60"),
KEEPFILTERS ( dailyKPI[Month - Year] IN {"September 2020","October 2020", "November 2020", "December 2020", "August 2021","September 2021","October 2021"} )
)
RETURN
SUM(dailyKPI[Sales]) - _Exclude--NOTE: if possible, apply the filters to the dimension table (not the fact table)
Hi,
The result of the average function will not get affected by blank cells but will get affected by 0's. If you can share your data and show the expected result, i can offer assistance.
