Forum Discussion

unclejemima's avatar
unclejemima
Post Patron
9 years ago

Average not working as expected

I've got a line graph showing Current Year Sales from a measure called "TotalSales".


If I use the Average Line function, the results are correct showing the average of the available data (Jan, Feb, Mar & April)

 

If take TotalSales measure and use Average DAX funtion, the number it returns is lower.

 

I've tried making TotalSales a Column instead of measure and then changing value to "Show value as average", and it still returns the wrong value.


Any idea's?

 

 

 

Need Help

5 Replies

  • unclejemima's avatar
    unclejemima
    Post Patron
    9 years ago

    I put the average calc into a table to view the results...

     

    Correct Actual : Jan 283, Feb 300, Mar 321, Apr 106. = 1010/4 Months = 252.5

     

    Using average measure is averaging sales in each month (I assume) and the final number is wrong.  Table view shows.

    Jan 249, Feb 261, Mar 215, Apr 165 = 229.  This is flat our wrong.  I'm not sure whats going on or where its getting the numbers from.

     

    I just want to take the monthly sales (actual) divide by number of months and that will give the correct number

    • unclejemima's avatar
      unclejemima
      Post Patron
      9 years ago

      Well getting close...I used 

       

      AveragedSales = calculate(ViewInvoice[TotalSales]/MONTH([Today]))

       

      The number is correct now...but I'm still thinking this is not the best way to do this?

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi unclejemima,

         

        As you haven't post your table structure, I just assume you have a table called "ViewInvoice" with a "Date" column and a "Revenue" column.

         

        So your [TotalSales] measure should be like below.

         

        TotalSales = SUM(ViewInvoice[Revenue])

        Then should be able to simply use the formula below to calculate the average sales. :smileyhappy:

         

        AveragedSales = AVERAGE(ViewInvoice[Revenue])

         

        Regards