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 ...
Need Help
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. 

     

    • adbm's avatar
      adbm
      Helper I
      1 year ago

      Hi Selva-Salimi 

       

      Thanks -- but sorry no luck still. see below

       

       

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

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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

      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.