Forum Discussion

adbm's avatar
adbm
Helper I
1 year ago

Mean value not calculated correctly

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

 

 

 

 

 

Need Help

7 Replies

  • Selva-Salimi's avatar
    Selva-Salimi
    Solution Sage
    1 year ago

    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's avatar
      adbm
      Helper I
      1 year ago

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

       

       

       

       

      • Selva-Salimi's avatar
        Selva-Salimi
        Solution Sage
        1 year ago

        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.