Forum Discussion

ElodiePignato's avatar
ElodiePignato
Regular Visitor
2 years ago

DAX formula - Average

Hello everyone !   I have an issue with a DAX formula.   I have a table named "Incomind documents". It's one row per ticket (workload), a column with the status (done, on hold, to treat, etc.) an...
Need Help
ElodiePignato's avatar
ElodiePignato
Regular Visitor
2 years ago

Thank you Dangar332 but I have this error message :

 

"The function AVERAGEX cannot work with values of type String.

Dangar332's avatar
Dangar332
Resident Rockstar
2 years ago

hi, ElodiePignato 

 

i think your   'incomind documents'[workload]  is string type. so it could happen

can you provide some sample data?

  • ElodiePignato's avatar
    ElodiePignato
    Regular Visitor
    2 years ago

    I finally used this measure:

     

     

    But it's showing me the exact same result than another formula where I calculate the incoming tickets from last month using "COUNTROWS" :

    • Dangar332's avatar
      Dangar332
      Resident Rockstar
      2 years ago

      HI, ElodiePignato 

      as i understand you want to avg of last 12 month with beginning of month of today
      if yes then follow below

       

      result =
var a = eomonth(today(),-13)+1   //  this give you  (11/1/2022)  as you want beginning of month of today with 12 month difference


var b = calculate(
          countrows(yourtablename),
          'Incomind documents'[reception date]>=a &&
          'Incomind documents'[reception date]<=today()
                  )  // give rows b/w  (11/1/2022 to 11/2/2022)

return 
divide(b,12)

      )

      • ElodiePignato's avatar
        ElodiePignato
        Regular Visitor
        2 years ago

        Hi Dangar332 this formula gives a whole different result :

         

         

        The previous DAX formula was "better".

         

        I checked with the table "Incoming documents" via Excel and this is the numer of incoming tickets per month :

         

        So the average should be around 5k