including blanks in an average with summarize

[KeyData]

Hi,

I´m trying to compute monthly sales average using summarize, but my formula is excluding blank values so it´s producing wrong results. How can i change the following dax formula to include and trate the blank values like 0s? 

SalesAvrg = Averagex(SUMMARIZE(Sales,SalesTime[YearMonth],"MonthlyQtty",sum(Sales[Quantity])),[MonthlyQtty])

i mean, if the summarize result in the following arrangement, the average should be 2 ((4+0+4+0)/4) and not 4 ((4+4)/2).

YearMonth	201701	201702	201703	201704
Qtty	4		4

[v-shex-msft]

Hi @KeyData,

You can refer to below steps to use query editor to create a summary table.

Steps:

1. Duplicate original table.
2. Unpivot date columns.
3. Group column with count and sum mode.
4. Add custom column to store average.

Steps

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCiwpqVTSUTIBYhgVqxOt5OjkDGQaQkSNgdhIKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [YearMonth = _t, #"201701" = _t, #"201702" = _t, #"201703" = _t, #"201704" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"YearMonth"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","","0",Replacer.ReplaceValue,{"Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"YearMonth"}, {{"Count", each Table.RowCount(_), type number}, {"Sum", each List.Sum([Value]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Avg", each [Sum]/[Count])
in
    #"Added Custom"

Regards,

Xiaoxin Sheng

[KeyData]

Hi v-shex-msft

Thks for your suggestion, but i need to make a lot of stat calculations (average, stddev, etc) with differents granulometry grades, so i would like to insist in find a solution with summarize.

[Ashish_Mathur]

Hi,

Try this

=AVERAGEX(SUMMARIZE(Sales,SalesTime[YearMonth],"MonthlyQtty",IF(ISBLANK(SUM(Sales[Quantity])),0,SUM(Sales[Quantity]))),[MonthlyQtty])

[KeyData]

Thks Ashiss, but it´s still not working. Your formula it´s not taking in account blanks like 0´s, so it´s given the average of just the months with sales.

[Ashish_Mathur]

Hi @KeyData,

Share the link from where i can download your file.

[KeyData]

Thks a lot for your interest Ashish

I tried to reproduce the issue in the following link

https://drive.google.com/file/d/1B5lsOIWfYM6Yk1oOpGU429RRbHFO8agX/view

It’s just an example, and for the average purpose, I created two measures

• MonthlyAvrgSalesQtty1 = divide(sum(Sales[SalesQuantity]),(max('Calendar'[DateKey])-min('Calendar'[DateKey])))*30

          This measure reproduce almost exactly the average that I’m looking for. (it’s not exactly because some months are not of               30 days)

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

         This is the one I want to include months withouth sales like 0 but is not working.

The example is based on Contoso DB.

[Ashish_Mathur]

Hi,

Please reduce that file size to a couple of MB's.

[KeyData]

hi Ashish,

Done. The Short example lynk is the following

https://drive.google.com/file/d/1_gu410ALDLz1HDkwAdXWknYXciK4fOPw/view?usp=sharing

Rgds.

[Ashish_Mathur]

Hi @KeyData,

Try this

=MonthlyAvrgSalesQtty1 = sum(Sales[SalesQuantity])/DATEDIFF(MIN('Calendar'[DateKey]),MAX('Calendar'[DateKey])+1,MONTH)

Hope this helps.

[KeyData]

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

[Ashish_Mathur]

Hi @KeyData,

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

[KeyData]

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])

[Ashish_Mathur · Accepted solution]

Hi,

See if this works

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

[jpt1228]

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.

Sum of Invoiced Cases	Week in year						179.2
Row Labels	31	32	33	34	35	36	37	38	39	40	Grand Total	Straight Invoiced for 10 weeks including blanks	number of blanks	not counting blanks
Item A		384	256	256	640		256				1792	179.2	5	358.4
Item B	140	140	180	180	80	40	140	120	180	240	1440	144	0	144
Item C	9450	9412	11886	9273	7453	8385	8768	10542	6574	9502	91245	9124.5	0	9124.5
Item D		1600	800	1760		1097	1360	2320	960	1120	11017	1101.7	2	1377.125

[Ashish_Mathur]

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.

[jpt1228]

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

[KeyData]

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

[Ashish_Mathur]

You are welcome.

[rajulshah]

Hello , 

You can try the below formula:

SalesAvrg = AVERAGEX(SUMMARIZE(Sales,SalesTime[YearMonth],Sales[Quantity],"MonthlyQtty",IF(ISBLANK(Sales[Quantity]),0,Sales[Quantity])),[MonthlyQtty])

Please let me know if you are not able to achieve what is needed.

[KeyData]

Hi rajushah,

i've tried your if clause suggestion before mi posting, but it didn't work. Your formula is still exluding blanks and i think is because the aggregation work of the summarize expression. Do you have another suggestion?