Forum Discussion

DaveyP's avatar
DaveyP
Frequent Visitor
8 years ago
Solved

Average Weekly Sales

Hi    I am having trouble working out an average sales DAX expression, it is for a group of stores where the data table (Keys) contains one line per store per week,   The issue i am facing is all...
Need Help
  • DaveyP's avatar
    DaveyP
    8 years ago

    GOT IT!!!

     

    AWUS Actual =
    VAR __CATEGORY_VALUES = VALUES('Keys'[MonDate])
    RETURN
     DIVIDE(
      SUMX(
       KEEPFILTERS(__CATEGORY_VALUES),
       CALCULATE(SUM('Keys'[Royalties]) / DISTINCTCOUNT('Keys'[Store Name]))
      ),
      SUMX(
       KEEPFILTERS(__CATEGORY_VALUES),
       CALCULATE(DISTINCTCOUNT('Keys'[MonDate]))
      )
     )

DaveyP's avatar
DaveyP
Frequent Visitor
8 years ago

Hi Dozer

 

Thanks for your reply but this still doesnt give the right result, it is still total sales/total distinct weeks and doesnt work when 1 store wasnt open for that week

 

Dave

ChrisMendoza's avatar
ChrisMendoza
Resident Rockstar
8 years ago

Hi DaveyP, may I ask what you expect the answer to be in your dataset that you provided?

  • DaveyP's avatar
    DaveyP
    Frequent Visitor
    8 years ago

    Hi Dozer

     

    Sorry to confuse, below is the example in more detail, the results i keep getting are essentially all stores sales added together (9300) divided by count of stores (2325) divided by count of weeks (581.25)

     

    WeekStore1Store2Store3Store4AWUS 
    1400500700600550(All stores sales divide by count of store (4))
    2500600800700650(All stores sales divide by count of store (4))
    3600700900800750(All stores sales divide by count of store (4))
    4700800  750(All stores sales divide by count of store (2))
           
         675

    Average sales = all weeks AWUS / Count of weeks

    • ChrisMendoza's avatar
      ChrisMendoza
      Resident Rockstar
      8 years ago

      DaveyP,

       

      Well this is interesting...adding an Average Line, You get the $675 but I do not know how it's calculated.

       

       

      • DaveyP's avatar
        DaveyP
        Frequent Visitor
        8 years ago

        GOT IT!!!

         

        AWUS Actual =
        VAR __CATEGORY_VALUES = VALUES('Keys'[MonDate])
        RETURN
         DIVIDE(
          SUMX(
           KEEPFILTERS(__CATEGORY_VALUES),
           CALCULATE(SUM('Keys'[Royalties]) / DISTINCTCOUNT('Keys'[Store Name]))
          ),
          SUMX(
           KEEPFILTERS(__CATEGORY_VALUES),
           CALCULATE(DISTINCTCOUNT('Keys'[MonDate]))
          )
         )