Dealing with blank data - creating measures

[Jrich18]

Hi,

Im realatively new to power BI, im using a flat file to report on price movements of commodities.

not all commodities have price data for each month.

My data table looks like this:

Commodity	Date	Price
1	01-Jan	1
1	02-Jan	2
1	03-Jan	3
2	01-Jan	1
2	03-Jan	2
2	04-Jan	3
3	01-Jan	1
3	05-Jan	2

I want to create measures to calculate things such as averag price and % change. However the average price takes into account the blanks and brings the average down. how can i get around this? ive tried the following which doesnt work in ignoring the blanks:

CALCULATE(AVERAGE('Weekly Master'[Price]), 'Weekly Master'[Price]<>0||'Weekly Master'[Price]<>BLANK())

Thanks!

[AlB · Accepted solution]

Hi @Jrich18 

AVERAGE already ignores the blanks (not the zeros, of course). Use an AND instead of an OR:

CALCULATE(AVERAGE('Weekly Master'[Price]), 'Weekly Master'[Price]<>0  &&  'Weekly Master'[Price]<>BLANK())

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.