Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Average count per month

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

 

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    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


4 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Fowmy
        Super User
        6 years ago

        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 

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    6 years ago

    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