cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Calculated column average help needed

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Calculated column average help needed

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 REPLIES 9
Super User IV

## Re: Calculated column average help needed

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

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Regular Visitor

## Re: Calculated column average help needed

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

Super User IV

## Re: Calculated column average help needed

Multiple by -1?

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Regular Visitor

## Re: Calculated column average help needed

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

Regular Visitor

## Re: Calculated column average help needed

Anyone please try, its an interesting problem...

Microsoft

## Re: Calculated column average help needed

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Calculated column average help needed

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

Frequent Visitor

## Re: Calculated column average help needed

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?

Super User IV

## Re: Calculated column average help needed

Hi,

Share a dataset and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements