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)

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

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's avatar
rbriga
Icon for Impactful Individual rankImpactful Individual
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)

  • Aburar_123's avatar
    Aburar_123
    Icon for Solution Supplier rankSolution Supplier
    4 years ago

    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,