Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
blytonpereira
Helper II
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
charleshale
Responsive Resident
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)

 

 

v-lili6-msft
Community Support
Community Support

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.

@blytonpereira

In fact the

Table1[Value] <> BLANK ()

is probably not necessary as AVERAGE( ) ignores blanks already

Omega
Impactful Individual
Impactful Individual

Can you share a sample of the expected result? 

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

@blytonpereira

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

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.

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

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

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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