Mean value not calculated correctly

[adbm]

Hi,

I have a table with ~24K rows of invoice data of various vendors. Im trying to add a calculated column to find the average of a column called 'Days to Payment' by vendor. So for every row, i shall have a result and the result will be the same in all rows of a particular vendor. I used the following formula in the column:

MeanDays =

VAR DaysAverage = CALCULATE(AVERAGE('Vendor data'[Days to Payment]),ALLEXCEPT('Vendor data','Vendor data'[Name]))

RETURN DaysAverage

By using this formula i do get the same result for all vendors, however it is not the correct result. E.g. For the data in the table below which is the data for a vendor X, i should be getting the result as 893.63; however i get 394 days. Can anyone tell me what's wrong with my formula please?

Thanks,

Natali

[Selva-Salimi]

Hi @adbm 

 you can just rewrite it as follows:

CALCULATE(AVERAGE('Vendor data'[Days to Payment]),filter('Vendor data','Vendor data'[Name] = earlier('Vendor data'[Name])))

If this post helps, then I would appreciate a thumbs up👍 and mark it as the solution to help the other members find it more quickly. 

[adbm]

Hi @Selva-Salimi  - thakns for that, but  it doesn't help unfortunately; still showing the same incorrect average value

[Anonymous]

Hi @adbm ,

I can't see what the exact data in the Name column is in your picture. But I created a table anyway.

I think you can try to create a calculated column and use this DAX code.

Column = 
CALCULATE(
    AVERAGE('Vendor data'[Days to Payment]),
    ALL('Vendor data')
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[adbm]

Hi @Anonymous 

Thanks for your response. the rows i've sent perian to only one vendor.. however i have a data set like that of multiple vendors so i need the average calculated by vendor. So in the table you;ve shown above i would need pbi to give me a a single same result for all rows of vendor A and a single same result for all rows of vendor B. Currently your formula is calculating the average of the entire table and not average by vendor which is what i need.

[Selva-Salimi]

@adbm 

Ok, then rewrite it as follows:

CALCULATE(Sum('Vendor data'[Days to Payment]),filter('Vendor data','Vendor data'[Name] = earlier('Vendor data'[Name]))) / CALCULATE(count('Vendor data'[Days to Payment]),filter('Vendor data','Vendor data'[Name] = earlier('Vendor data'[Name])))

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly. 

[adbm]

Hi @Selva-Salimi 

Thanks -- but sorry no luck still. see below

[Selva-Salimi]

@adbm 

please go to Direct query, refresh your data and filter this table to the "Name" that was shown on the previous image you shared and then share that data. I am sure that the problem is not in formula. it might be in your data.