Getting correct Average Sales Quantity by Week when data is blank

[carguello726]

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.

[v-yingjl]

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]

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]

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.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490