Forum Discussion
Average Measure with filter
Hi,
I want to calculate the average days it take to complete an order with product type 'Computer'. How can I calculate that in a measure?
This is a sample of my data:
| OrderID | OrderDate | OrderCompleteDate | Product |
| 1 | 1-11-2021 | 5-11-2021 | Computer |
| 2 | 4-11-2021 | 7-11-2021 | Computer |
| 3 | 7-11-2021 | 11-11-2021 | Computer |
Anonymous
you can create a column and measure
days = DATEDIFF('Table'[OrderDate ],'Table'[OrderCompleteDate ],DAY) Measure = AVERAGEX(FILTER('Table','Table'[Product ]="Computer"),'Table'[days])or you can create a measure directly.
Measure 2 = VAR tbl=ADDCOLUMNS(FILTER('Table','Table'[Product ]="Computer"),"day2",DATEDIFF('Table'[OrderDate ],'Table'[OrderCompleteDate ],DAY)) return AVERAGEX(tbl,[day2])pls see the attachment below.
6 Replies
Anonymous
you can create a column and measure
days = DATEDIFF('Table'[OrderDate ],'Table'[OrderCompleteDate ],DAY) Measure = AVERAGEX(FILTER('Table','Table'[Product ]="Computer"),'Table'[days])or you can create a measure directly.
Measure 2 = VAR tbl=ADDCOLUMNS(FILTER('Table','Table'[Product ]="Computer"),"day2",DATEDIFF('Table'[OrderDate ],'Table'[OrderCompleteDate ],DAY)) return AVERAGEX(tbl,[day2])pls see the attachment below.
ryan_mayu Thanks, question: in the second measure, why do you define "day2"?
Anonymous ,
days2 is defined here as a column for the difference of those tow date columns. You can use othe column name instaed. One first measure dates difference created as a column whereas on 2nd calculation written as a measure, so he is using variable and ADDCOLUMNS functions to define that on measure and use that on AVERAGEX
Hi,
You may create a calculated column formula to calculate the difference between the 2 date columns. Give a heaing to that column as Days. Then write this measure
Measure = average9Data[Days])
To your table visual, drag Product and the Measure.
