cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## including blanks in an average with summarize

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
1 ACCEPTED SOLUTION
Super User

Hi,

See if this works

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
20 REPLIES 20
Community Support

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}}),
in

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

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.

Super User

Hi,

Try this

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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.

Super User

Hi @KeyData,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Thks a lot for your interest Ashish

I tried to reproduce the issue in the following link

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.

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

hi Ashish,

Done. The Short example lynk is the following

Rgds.

Super User

Hi @KeyData,

Try this

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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

Super User

Hi @KeyData,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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

Super User

Hi,

See if this works

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Responsive Resident

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
Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Responsive Resident

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

Frequent Visitor

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.

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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.

Frequent Visitor

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors