5 Replies Latest reply: Jan 3, 2018 11:57 PM by kaan erisen RSS

    Count Distinct HELP!!!

    Mario Cueva



      I'm trying to use the following expression to express the sales avg considering the number of months.




      Sum Sales in last 11 years = 654,229,440

      Months for the period of time selected = ex: 11 years = 132 months (I have an expresión called Year-Month for every sales month "2017-01-Jan","2017-02-Feb" etc)


      My expression is: =Sum(Subt)/(Count(DISTINCT{$<[Year]-={'2006'}>}[Year-Month]))

      If there's no selection the expression works


      654,229,440 / 132 = average sales per month


      But when I select one specific year or more it still divides sales by 132 months, considering that if I select 1 year the formula should be:


      654,229,440 / 12 = average sales per month

      Or if I select 2 years:


      654,229,440 / 24 = average sales per month

      And so on, what am I doing wrong?