convert measures to column for correct average.

[Mous007]

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

[amitchandak]

@Mous007 , check this file you have a measure that using workday from the calendar

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

Refer to 2nd Page

[Mous007]

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 · Accepted solution]

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]

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

[mahoneypat]

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]

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