Forum Discussion

carguello726's avatar
carguello726
Frequent Visitor
6 years ago

Getting correct Average Sales Quantity by Week when data is blank

Hi,

 

I am trying to capture the average sales quantity per week by product. The challenge is to include the weeks when there is no data.

 

I know that i will  need to use ALL or VALUES for the week # but I still cannot get the desired result. In the example below, both products should take an average accross 3 weeks, even though product B does not have any sales (row item)  in week 3 - a zero should be placed in week 3. Any help is greatly appreciated. Thank you.

 

 

 

Need Help

4 Replies

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    Hi carguello726 ,

    I’m not certain the structure of your table so I can provide two ways for this case:

    If the table structure is like this, you can create the following measure simply:

    AVG Qty = SUM('Table'[Quantity])/ 3

     assume the week is 3 or more than 3 but have no whole row data in table, just modify the denominator

    If the table structure is like this, you can create the following measure:

    AVG Qty = SUM('Table'[Quantity])/ CALCULATE(DISTINCTCOUNT('Table'[Week #]),ALLSELECTED('Table'))

    in this way the denominator is dynamic

    This may be your expected output:

    Best Regards,

    Yingjie Li

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

    • carguello726's avatar
      carguello726
      Frequent Visitor
      6 years ago

      Thank you so much. My table is like your first example where the week (entire row) is missing.

       

      I would prefer to have a dax function that uses the AVERAGE function instead of manually inputting the denominator. This is because I will also need to capture standard deviation (which will need to include the missing week).

      • v-yingjl's avatar
        v-yingjl
        Community Support
        6 years ago

        Hi carguello726 ,

        I dont think Average function can match for this case since the entire row is missing. You can also use the second measure to get the same result. If you want to calculate standard deviation, you can set the measure result as a variable to use.

         

        Best Regards,

        Yingjie Li

         

        If this post helps then please consider Accept it as the solution to help the other members find it more quickly.