Average of column filtered by month

[mrizvi]

Hello all,

I am working with a data set that has three columns (designed below) - 1) Order date - 2) "Was the order included" - 3) Site Name. 

Order Date	Was the order included?	Site Name
09/02/2020	Y	Danville
10/05/2020	N	Clinch
10/22/2020	N	Dallas North
11/22/2020	Y	Dallas North
11/09/2020	Y	Dallas North

What I'm trying to do is use the "Was the order included" column to get an average score of the number of orders that were included (ie "Y" for that row) filtered out for each month. 
For example: If for the month of October -- Clinch had a total of 50 orders and 45 of them were included (ie 45 with "Y" and 5 with "N") then the average for that month would show as 90% 

How can I set this formula up for my report?

Thanks

M.R

[v-xulin-mstf · Accepted solution]

Hi, @mrizvi 

Depending on your needs, the following Measure is recommended：

Measure =

var _month = MONTH(SELECTEDVALUE('Table'[Order Date]))

var a =

CALCULATE(

    COUNTROWS('Table'),

    FILTER(

        ALL('Table'),

        'Table'[Was the order included?] = "Y" &&

        MONTH('Table'[Order Date]) = _month

    )

)

var b =

CALCULATE(

    COUNTROWS('Table'),

    FILTER(

        ALL('Table'),

        MONTH('Table'[Order Date]) = _month

    )

)

return

a / b

The result is as follows:

Here is the sample file that you can refer:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/admin_qiuyunus_onmicrosoft_com/EVuLjL9r7khIiwUFyjb...

Best Regards,

Link Chen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@mrizvi , Create a measure like this and try

divide(calculate(countrows(Table), filter(Table,Table[Was the order included] ="Y")),countrows(Table))