Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
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))
)
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 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 thanks so much for replying, but that is throwing an error saying "The syntax for ';' is incorrect."
Any idea why that might be?
@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 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?
I personally try to avoid the AVERAGE function.
You can simply calculate it in the mathematical way.
sum(filtered)/count(unfiltered)
@Anonymous so that would be this?
CALCULATE(DIVIDE,(SUM(FILTER(Table,[Column D]>0))),(COUNT(FILTER(Table,[Column D]>0))))
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 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?
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 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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
85 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
132 | |
110 | |
65 | |
55 |