Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
Solved! Go to 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. |
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. |
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
8 | |
6 |