including blanks in an average with summarize

Do you have any idea why the summary options do not work?

Hi KeyData,

I am not sure of whom you replied to but did my proposed solution work?

Hi Ashis.

Thanks a lot for your interest. Your solution porpoused of calculate the average like the sum of sales over the count of the months in the data range is correct, but i need a solution with the summarize clause, because another statistical measures are needed.  I should have  posted this discussion for example with devstat function, wich is more difficult to calculate in an alternative way like the v-shex or your last one.

How can we use the next expression includind in the calculation blanks like 0s.?

SalesStDev= STDEVX.P(SUMMARIZE(Sales,SalesTime[YearMonth],"MonthlyQtty",sum(Sales[Quantity])),[MonthlyQtty])

Hi Ashish

Thanks a lot. That´s the solution. The clause works in the following way

= AVERAGEX(SUMMARIZE('Calendar',Calendar[YearMonth],"MonthlyQtty",IF(ISBLANK(SUM(Sales[SalesQuantity])),0,SUM(Sales[SalesQuantity]))),[MonthlyQtty])

Like you suggested, it´s imperative to invoque the Calendar Table instead the Sales one.

Again, Thank you very much for your interest and your help

You are welcome.

Hello Ashish_Mathur I am trying to replicate your formula suggestion to include blanks in the average. I have a date table (Dimdate) with [weekinyear] granularity for sales. I also have an invoiced lines table (invoicedlines) with [Cases Invoiced]. I need to calculate the value of invoiced cases INCLUDING weeks there are no invoiced cases. 

Item A: Sold 1,792 cases over 5 weeks so the result should be: 1,792/10=179.2 But my current calculation is ignoring the zero weeks. So the result it is returning is 1,792/5 = 358.4 which is NOT CORRECT.

Hi,

Share the link from where i can download your PBI file.  Please ensure that the file size is only a couple of MB's.

Ashish_Mathur Hello, I cannto share the PBI file - it is connected to a large data model with sensitive data.