Forum Discussion

Mous007's avatar
Mous007
Icon for Helper IV rankHelper IV
6 years ago
Solved

convert measures to column for correct average.

Hi all,   I have two measures created and they are workiing fine for me so far. i tried to average one of them but i am getting the wrong average. My measures are below: 1st to count difference ...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Assuming you have only measures in 'Measures Table', that will not work.  Typically, you would use AVERAGEX() to iterate over a Dimension table or a virtual table like VALUES() or SUMMARIZE().  For example,

     

    Measure = AVERAGEX(DimensionTable, [YourMeasure])

     

    Measure2 = AVERAGEX(VALUES(Product[ProductID]), [YourMeasure])

     

    What is your goal in averaging over the Measures table?

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

Mous007's avatar
Mous007
Icon for Helper IV rankHelper IV
6 years ago

hi amitchandak , for some reason i cannot open/see the file on the link.

 

Appreciate you tried to help, thanks

 

But so far im getting the correct results for my working days, i am just facing an issue averaging the measures above for working days.

 

i am using the averagex formula:

 

CALCULATE(AVERAGEX('Measures table' , [Duration (WD's)]) , 'records table'[STATUS] = "Complete")
 
but the results are completely wrong. is it maybe because i am trying to average a measure (fro m my measures tables) and trying to filter based on a column (from a different table) ?
  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Assuming you have only measures in 'Measures Table', that will not work.  Typically, you would use AVERAGEX() to iterate over a Dimension table or a virtual table like VALUES() or SUMMARIZE().  For example,

     

    Measure = AVERAGEX(DimensionTable, [YourMeasure])

     

    Measure2 = AVERAGEX(VALUES(Product[ProductID]), [YourMeasure])

     

    What is your goal in averaging over the Measures table?

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • Mous007's avatar
      Mous007
      Icon for Helper IV rankHelper IV
      6 years ago

      Hi mahoneypat , I had a table with the request number and the working days needed to complete each single request. 

       

      So based on your reply, i realized that I had to switch my measures from the measures tables to the actual data table. i used the same average calculation and now the average is calculated correctly.

       

      Thanks a lot for the concept clarification.

       

      Best,

      Mous