Average count per month

[Anonymous]

I have a date table.

We have our orders that come in daily, although some days no orders come in.   I need to determine the average number of orders per month.  Using order ID. 

so a simple example below.  If the first 4 days of a month had these counts of distinct orders, determine the average number of orders that month.   So the measure should count days with no orders.   In this example the month average would be 4.25.

5

0

8

4

[v-easonf-msft · Accepted solution]

Hi , @Anonymous 

If no orders come in on a certain day of a month and that day will still be counted,you can try measure as below:

Measure2 = 
var countRow=CALCULATE(COUNT('Table'[Day of month]),ALL('Table'))
var days=DAY(EOMONTH(SELECTEDVALUE('Table'[Date]),0))
return DIVIDE(countRow,days)

pbix attached

Best Regards,
Community Support Team _ Eason

[Fowmy]

@Anonymous 

Simple Average will do as, by default, Average function will not consider blank, but if you want to avoid zero then

Use

Measure = CALCULATE(
    AVERAGE('Table'[Column1]),
    'Table'[Column1]>0)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[Anonymous]

Hi @Fowmy    I am counting text strings, not values.  So this does not seem to work.  

For example:    First 4 days of the month.    4 distinct orders  (two on the 1st day of the month).     So, 5 orders in 4 days.     Average orders per day.  1.25

Day of Month     Order ID 

1                            1234

1                            1237

2                            3490

3                            3456

4                            3457

[Fowmy]

@Anonymous 

Try this Measure:

Measure = 
DIVIDE(
    COUNT(ORDER_TABLE[ORDER ID]),
    DISTINCTCOUNT(ORDER_TABLE[DATE]))

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn