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)

rbriga's avatar
rbriga
Icon for Impactful Individual rankImpactful Individual
4 years ago

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'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,