- Power BI forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: How to calculate average only for certain valu...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

How to calculate average only for certain values?

12-13-2018
06:04 AM

So I have inherited from another user a table with a couple of columns that calculate the difference between two other columns and then replaces all negative values with zero, as follows:

Column C = datediff('Table'[Column B].[Date],'Table'[Column A].[Date],DAY))

Column D = if('Table'[Column C]>0,'Table'[Column C],0)

There is then a visual which displays the average of Column D via the following Measure:

Measure = CALCULATE(AVERAGE(Table[Column D]))

The problem with this is that the zeroes in Column D are artificially pulling the average down (Column D is a "days late" kind of metric, so we shouldn't be including zeroes). How can I go about changing the measure to only calculate the average for non-zero values? I tried using FILTER and WHERE statements as follows with no success (syntax errors):

Measure = WHERE(Table[Column D]>0),CALCULATE(AVERAGE(Table[Column D]))

Measure = CALCULATE(AVERAGE(FILTER(Table,[Column D]>0)))

It is probably pretty obvious from my question that I am a complete newbie to DAX syntax. Could someone please help? I'm sure there's probably a MUCH simpler way of accomplishing this task, I just don't know the language to do it. Thanks in advance!

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-13-2018
07:46 AM

Of what I can make from you starting text the measure should be something like this:

DIVIDE(

SUM('Table'[Column D]),

CALCULATE(COUNT('Table'[Column D]),FILTER(ALL(Table);'Table'[Column D] >0))

)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-13-2018
12:17 PM

UPDATE! SUCCESS!

@Anonymous you got me VERY close, the solution ended up being what you gave me, minus the "All" in the filter:

= DIVIDE(SUM('Table'[Column D]),CALCULATE(COUNT('Table'[Column D]),FILTER(Table,'Table'[Column D]>0)))

I think the "All" was negating the filter somehow.

Anyway, big thanks to you and @Anonymous! Problem Solved!

10 REPLIES 10

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-13-2018
06:57 AM

@Anonymous Don't worry is a simple Measure, just keep studying DAX because is amazing.

CALCULATE(AVERAGE(Table[Column D]);FILTER(Table,[Column D]>0))

Good Luck!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-13-2018
07:01 AM

@Anonymous thanks so much for replying, but that is throwing an error saying "The syntax for ';' is incorrect."

Any idea why that might be?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-13-2018
07:05 AM

@Anonymous wrote:@Anonymous thanks so much for replying, but that is throwing an error saying "The syntax for ';' is incorrect."

Any idea why that might be?

Change the ';' into ',' might depend on region setting which one you need to use.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-13-2018
07:23 AM

@Anonymous That resolved the syntax error, but did not change the result of the calculation. The average should go way up with all the zeroes taken out, should it not?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-13-2018
07:31 AM

I personally try to avoid the AVERAGE function.

You can simply calculate it in the mathematical way.

sum(filtered)/count(unfiltered)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-13-2018
07:37 AM

@Anonymous so that would be this?

CALCULATE(DIVIDE,(SUM(FILTER(Table,[Column D]>0))),(COUNT(FILTER(Table,[Column D]>0))))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-13-2018
07:46 AM

Of what I can make from you starting text the measure should be something like this:

DIVIDE(

SUM('Table'[Column D]),

CALCULATE(COUNT('Table'[Column D]),FILTER(ALL(Table);'Table'[Column D] >0))

)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-13-2018
08:10 AM

@Anonymous we're getting so close! that expression ran when I changed it to:

= DIVIDE(SUM('Table'[Column D]),CALCULATE(COUNT('Table'[Column D]),FILTER(ALL(Table),'Table'[Column D]>0)))

but it bizarrely dropped the average way *down* which would suggest to me it has somehow *increased* the count of values being averaged. Any ideas?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-13-2018
12:17 PM

UPDATE! SUCCESS!

@Anonymous you got me VERY close, the solution ended up being what you gave me, minus the "All" in the filter:

= DIVIDE(SUM('Table'[Column D]),CALCULATE(COUNT('Table'[Column D]),FILTER(Table,'Table'[Column D]>0)))

I think the "All" was negating the filter somehow.

Anyway, big thanks to you and @Anonymous! Problem Solved!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-13-2018
08:16 AM

@Anonymous Correction: I confirmed the COUNT function executed correctly and came up with the correct count of values by just running the COUNT function separately. I then ran the SUM function separately and found that the sum is undercalculated by about 966,000....how is this possible? I dumped Column D into Excel and calculated the sum myself and it should be 988,636 but Power BI is somehow only coming up with 22,216...what gives?

Announcements

Check out the October 2024 Power BI update to learn about new features.

Learn from experts, get hands-on experience, and win awesome prizes.

Featured Topics

Top Solution Authors

User | Count |
---|---|

110 | |

98 | |

96 | |

87 | |

68 |

Top Kudoed Authors

User | Count |
---|---|

167 | |

134 | |

129 | |

103 | |

96 |