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

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

Reply
Anonymous
Not applicable

How to calculate average only for certain values?

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!

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

 

View solution in original post

Anonymous
Not applicable

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!

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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

Anonymous
Not applicable

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

 

Any idea why that might be?

Anonymous
Not applicable


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

Anonymous
Not applicable

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

Anonymous
Not applicable

I personally try to avoid the AVERAGE function.

 

You can simply calculate it in the mathematical way. 

sum(filtered)/count(unfiltered)
Anonymous
Not applicable

@Anonymous so that would be this?

 

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

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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

Anonymous
Not applicable

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!

Anonymous
Not applicable

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

October NL Carousel

Fabric Community Update - October 2024

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