cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
blytonpereira Regular Visitor
Regular Visitor

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

9 REPLIES 9
Omega Established Member
Established Member

Re: Average excluding Zeros and Blanks

Can you share a sample of the expected result? 

blytonpereira Regular Visitor
Regular Visitor

Re: Average excluding Zeros and Blanks

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

Re: Average excluding Zeros and Blanks

@blytonpereira

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

blytonpereira Regular Visitor
Regular Visitor

Re: Average excluding Zeros and Blanks

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

Re: Average excluding Zeros and Blanks

@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] )
    )
)
blytonpereira Regular Visitor
Regular Visitor

Re: Average excluding Zeros and Blanks

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.

https://drive.google.com/open?id=1E0oepiNZ90At46NrCXzFQdPQWPMVAFK4

 

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

Re: Average excluding Zeros and Blanks

@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'     

Community Support Team
Community Support Team

Re: Average excluding Zeros and Blanks

hi, @blytonpereira

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.

3.JPG4.JPG

 

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],
			STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)),
			__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
Super User

Re: Average excluding Zeros and Blanks

@blytonpereira

In fact the

Table1[Value] <> BLANK ()

is probably not necessary as AVERAGE( ) ignores blanks already