Exclude values from Average calculation

[Nanakwame]

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 

[Ashish_Mathur]

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.

[rbriga]

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})

)

[Nanakwame]

@rbriga 

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"}))))

[rbriga · Accepted solution]

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)

[Aburar_123]

Hi, 

You can try unchecking the "Show items with no data" option.

For Eg., in the below image i dont have any sales for the Descripton "ccc". since the "Show items with no data" has been checked it shows that record. 

see it after uncheck this option,