Forum Discussion

Nanakwame's avatar
Nanakwame
Icon for Helper II rankHelper II
4 years ago
Solved

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, th...
Data Protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • rbriga's avatar
    rbriga
    4 years ago

    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)

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
4 years ago

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.