Calculated column average help needed

[sabedin]

Hi,

I have two Date columns and the difference of these two columns in Excel (all negative numbers).

Then I created a Column using simple DAX formula to substract these columns just like in Excel and then changed the format of the column to Whole Number. I got same result so it is OK up till now.

Now, when I create a measure to calculate the average of these values, I get two different result, a negative result when I use the column calculated in Excel (correct result) and a positive result when I use the column created with DAX!!! Apparently there is a problem with the column format and I don't know how to fix it.

Thank you for your help

[Greg_Deckler]

Not sure what is going on, couldn't replicate it. I created a column with negative values and a measure to average it and got a negative. Can you post sample data and your formulas? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Here is what I did:

"Negative" query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jU0tVCK1QExTEygDLgIilQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Days ATF" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Days ATF", Int64.Type}})
in
    #"Changed Type"

Measure:

Measure 3 = AVERAGE(Negative[Days ATF]) 

[sabedin]

I created a sample source file and pbix file showing discreppancies. Find them here: Files

It looks like the problem is with the rows that contain errors, because when I removed them in Excel source file the result was OK!!! 

What to do now so I could get the correct result (negative one) without removing the rows in the source file?

Thanks

[Greg_Deckler]

Multiple by -1?

[sabedin]

Please check the attached files, its a problem with the rows that have errors on it...

[sabedin]

Anyone please try, its an interesting problem...

[v-jiascu-msft · Accepted solution]

Hi @sabedin,

That could be caused by the way how formulas handle the blank values. Please try out this formula.

Column = DATEDIFF([INVOICE_DATE],[ORDER_DATE],DAY)

Best Regards,

Dale

[sabedin]

@v-jiascu-msft

Hi Dale,

Yes, now the averages are correct! Thanks a lot. I will need to learn this in more detail so to not have this kind of troubles in the future.

[Anonymous]

Hi, we have similar issue and we want to get some help from the xperience in this thread.  Is that something possible? 

Issue:

We have to calculate Average on set of values based on a date range.  

If we take total values (entire date range that we have data for), the average is calculated correctly.  But, when we select a date range, the average calculate is failing.

Any help?

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.