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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Jrich18
Frequent Visitor

Dealing with blank data - creating measures

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:

CommodityDatePrice
101-Jan1
102-Jan2
103-Jan3
201-Jan1
203-Jan2
204-Jan3
301-Jan1
305-Jan2

 

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!
1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

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

 

 

SU18_powerbi_badge

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.

 

 

View solution in original post

1 REPLY 1
AlB
Community Champion
Community Champion

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

 

 

SU18_powerbi_badge

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.

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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