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 in days and 2nd for difference but only counting working days.

 

Duration (non WD's) =
DATEDIFF (
    MAX ( 'table1'[startdate] ),
    MAX ( 'table1'[enddate] ),
    SECOND
) / ( 3600 * 24 )
 
Duration (WD's) =
[Duration (non WD's)]
- CALCULATE (
COUNT ( 'Date table'[Date] ),
FILTER (
'Date table',
'Date table'[Weekday number] in {6,7}
&& 'Date table'[Date]
>= MAX ( 'table1'[startdate] )
&& 'Date table'[Date]
<= MAX ( 'table1'[enddate] )
))
 
Anyone to help me convert these two measures into columns please? or any other alternative please?
 
Best,
Mous
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

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    I would stick with measures unless you will be using the column on the axes, legend, etc. 

     

    Did you shift the WEEKDAY() in your Date table?  If not, your expression is counting Fri and Sat (Sun is WEEKDAY() = 1 by default).  Does that fix it?  

     

    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 , 

       

      please see my date table code below:

       

      Date table =
      VAR MinYear = YEAR ( MIN ( 'date Table'[startdate]))
      VAR MaxYear = YEAR ( MAX ( 'date table'[startdate]) )
      RETURN
      ADDCOLUMNS (
      FILTER (
      CALENDARAUTO( ),
      AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
      ),
      "Calendar Year", YEAR ( [Date] ),
      "Month Name", FORMAT ( [Date], "MMMM" ),
      "Month Number", MONTH ( [Date] ),
      "Weekday", FORMAT ( [Date], "ddd" ),
      "Weekday number", IF(WEEKDAY( [Date] )=1,7,WEEKDAY([Date])-1),
      "Period", FORMAT( [Date], "MMM-YYYY" ),
      "Sorting column", FORMAT( [Date], "YYYYMM" ),
      "Week number" , WEEKNUM([Date],2),
      "Week", "W" & FORMAT( [Date], "WW-MMM-YY" ),
      "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1)
       
      I believe my week according to the code above starts on Monday ?
       
      Any chance to switch to columns instead of measures ? because that might easily solve my issue
    • 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