cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper II

## Average excluding Zeros and Blanks

Hi everyone,

I need some help writing a DAX measure to CALCULATE the average excluding ZEROS AND blanks ?

My data is arranged in the following manner:

Brand      Period            Value

X               2018M01       2

X               2018M01       0

Y               2018M02       3

Z               2018M02       0

The AVERAGE should be calculated by BRAND and for a period range of the LAST 12 Months i.e 2018M01.......up until 2018M12.

The formulas should not count any PERIODS that have Blank values or Zeros.

The last 12 Months should be dynamic. using my Offsets Column.

The OFFSETS in my calendar for EXAMPLE the last month is -1 and previous  month-2 etc.

So I am assuming somewheer in the AVERAGE formula, we need to include this offsets for last 12 months so >=-12 and <=-1 ?

Thanks

10 REPLIES 10
Responsive Resident

I have a better / favorite way here if you are dealing with measures -- in case this helps!

``````FIXED AVERAGE =
VAR _Numerator = [//Measure1]+[//Measure2]+[//Measure3]
VAR _Denom =
VAR _1 = if (ISBLANK([//Measure1]), 0, 1)
VAR _2 = if (ISBLANK([//Measure2]), 0, 1)
VAR _3 =  if (ISBLANK([//Measure3]), 0, 1)
RETURN
_1+_2+_3
RETURN
_Numerator / _Denom)``````

Community Support

After my research, you may try this way:

1. for excluding Zeros and Blanks, you could add a conditional like this

`Measure = CALCULATE(AVERAGE(Table1[Sales]),Table1[Sales]<>0||Table1[Sales]<>BLANK())`

2. for rolling 12 months, you'd better use a date column instead of Period (eg. 2018M01) with Time-intelligence functions.

For example, you could convert  Period to date (eg. 2018M01 ->1/1/2018), then create a relationship with the calendar table. then calculate rolling 12 months average. of course, you could add period/ year/ month column in the calendar table too.

here a blog for you refer to:

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

By the way, you could use built-in New quick measure and select rolling average to quick and easily create this measure.

When use this way create the measure, you need to add the conditional to exclude Zeros and Blanks manually.

```Sales rolling average =
IF(
ISFILTERED('Table1'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('Table1'[Date].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Table1'[Date].[Date],
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Table1'),
'Table1'[Date].[Year],
'Table1'[Date].[QuarterNo],
'Table1'[Date].[Quarter],
'Table1'[Date].[MonthNo],
'Table1'[Date].[Month]
),
__DATE_PERIOD
),
CALCULATE(SUM('Table1'[Sales]), ALL('Table1'[Date].[Day]),Table1[Sales]<>0||Table1[Sales]<>BLANK())
)
)```

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

@blytonpereira

In fact the

`Table1[Value] <> BLANK ()`

is probably not necessary as AVERAGE( ) ignores blanks already

Impactful Individual

Can you share a sample of the expected result?

Helper II

A sample of the expected result I am looking for is as below:

Brand     Average Last 12 Months

X                 the average

Y               the average

Z                  the average

Super User

@blytonpereira

What is the question exactly? How to deal with the blanks or all the rest?

Helper II

The questions is how to write the DAX measure to calcualte the average of the last 12 Periods, ignoring any zeros AND blanks ??

The last 12 months should be dynamic and hence in the measure the offsets column that I have should be included.

Super User

@blytonpereira

Here's one option . The code below requires a 'Date' table, that you probably have already.

Haven't tested it. If you provided a sample of your data I could but in any case you get the idea.

```Measure =
CALCULATE (
AVERAGE ( Table1[Value] );
Table1[Value] <> BLANK ();
Table1[Value] <> 0;
DATESBETWEEN (
'Date'[Date];
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) );
LASTDATE ( 'Date'[Date] )
)
)```
Helper II

Hi

I treid this method but it does not seem to work in my dataset. Please see the attached EXCEL example file for the data.

I am linking the Period column in my Data tab with the Period column in my Calendar tab. (unique values) = Many to One relationship

Then in the average DAX measure I would like to apply some filters:

Profile = "STAT" or Profile = "MI"

Offset >=1 or <= 12 to select the next 12 months

Thanks

Super User

@blytonpereira

Nope, it cannot work as the set is organised. You'd need to create a proper (standard) Calendar table with a YearMoth field if you want to keep using the period names you have. The Offset field should be part of the your 'Data' table, not part of 'Calendar'

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors