Averages of Count

[Anonymous]

Hello Community, 

I have a list of Order IDs on my orders table.   Counting these up as distinct IDs  (so I do not count the same Order ID over multiple order lines) is easy.   I have done that already.    What I am trying to do now is to get an average of that count, at different granulatries.   

For example:   I have 3400 unique orders in a year.    

What I am struggling with is how to get an average, by month, week, day that I could use in a card visual.   Or on three separate card visuals.   For example, three card visuals:    Average Orders Per Day:  23     Average Orders Per Week:   77      Average Orders Per Month:   303      (just making up numbers)

Proving to be more difficult than I thought.   I have a date table connected to my orders table.  

Thanks in advance for any guidance!

[Greg_Deckler]

@Anonymous - This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

[hohlick]

Is this suitable to your task?

Average orders per day =

DIVIDE(

DISTINCTCOUNT ( Table[OrderID]), COUNTROWS(Calendar)

)

And for weeks it is 

Average orders per week =

DIVIDE(

DISTINCTCOUNT ( Table[OrderID]), COUNTROWS(SUMMARIZE(Calendar, Calendar[Year], Calendar[Week]))

)

[mahoneypat]

You can use AVERAGEX like this

Daily Average = AVERAGEX(VALUES('Date'[Date]), [DistinctCountMeasure])

Monthly Average = AVERAGEX(VALUES('Date'[Month]), [DistinctCountMeasure])

Note that this has to reference and existing [Measure].  If you put just DISTINCTCOUNT(Table[Column]), you would have to wrap it in a CALCULATE().

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[hohlick · Accepted solution]

@mahoneypat 

I think it is better combined

Per month = Averagex(summarize(Calendar, Calendar[Year], Calendar[Month]),[DistinctMeasure])

Per week =

Averagex(

summarize(Calendar, Calendar[Year], Calendar[WeekNum]),[DistinctMeasure])

[Anonymous]

@hohlick    @mahoneypat    @Greg_Deckler       Everyone...thank you for the quick responses and help.   Much appreciated!

In the end I went with the solutions like this from @hohlick  and they worked just fine for what I needed.    Putting day, week, and monthly averages on their own distinct card visuals.   

Using this as the measure inside of the formulas provided:    

Distinct Count Orders = COUNTROWS(VALUES(Orders[Order]))

Here is the formula (for the week) provided by @hohlick 

Avg Orders Per Week = AVERAGEX(SUMMARIZE(DateTable, DateTable[Year], DateTable[Week Number]),[Distinct Count Orders])

I also added a "day" measure, to give me the average orders per day: 

Avg Orders Per Day = AVERAGEX(SUMMARIZE(DateTable, DateTable[Date]),[Distinct Count Orders])