Forum Discussion
Getting correct Average Sales Quantity by Week when data is blank
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])/ 3assume 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.
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).
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.