Daily average

[chfio]

1) I have a dates column..example

1each    Dates

 1          3/5/2017

 1          3/5/2017 ---- 2 instances on 3/5

 1          3/9/2017

 1          3/9/2017

 1          3/9/2017

 1          3/9/2017 ---- 4 instances on 3/9

 1          3/10/2017 ---- 1 instance on 3/10

 1          4/18/2017

 1          4/18/2017

 1          4/18/2017 ---- 3 instances on 4/18

I have many other columns to optionally use but not sure if needed. Example i have a unique number on each row.

The goal is generate a card showing the average of instances / day.

I've tried a few things, asked peers, searched forums. I get how ridiculously simple this must be but thank you in advance for wildly explained how to solve. 

UPDATE....

Maybe solved. I created a new table. Entered the following

NewTable = SUMMARIZE(OrigTableName,OrigTableName[Dates].[Date],"dayavg",SUM(OrigTableName[1each]))

The "1each" column has a 1 for each row. I guess this is grouping by date and summing the 1s and .. summarizing that?

2) Still need to get the slicer on dates to update the average. Is that possible

[TomMartens · Accepted solution]

Hey,

unfortunately calculated tables used in the data model and not as a filter or input for other functions do not update depending on changes in a slicer selection, this is by design.

I created a little Power BI file that uses this measure

average no of rows per day = 
var maxSelectedDate = ALLSELECTED('Table1'[Date])
return
AVERAGEX(maxSelectedDate
	,CALCULATE(count('Table1'[RowID]))
)

this measure can used on card visual, the following screenshot shows that there are an average number of rows of 2.5 for both selected days (4 from 28th of August and 1 from 1st of September.

Hope this is what you're looking for.

Regards

[chfio]

Thanks TomMartens.

The PBI measure you created seems to update with the slider. I understand you're saying it isn't designed to work this way, I guess the resulting number could be wrong, but it is updating when i move the slider on the slicer. 

Let me know what you think?

 (in any case I'll have hundreds of days, check boxes may be difficult.)

As an aside, I'm trying to implement this for a milliseconds column too. I think im close but may open a new thread for that, if appropriate. 

Best

[TomMartens]

Hey,

sorry for not being clear. Calculated tables are not designed for the direct usage in visuals, as your example using SUMMARIZE(...)

The measure I provided is explicitely designed for multiselections (the date slider selects all dates between strart and end). This selection is stored in the variable maxSelectedDate (I have to admit that a more proper name for the variable would have been "selecteddates".

This table is used as table for the iterator function AVERAGEX(...), for each date the instances of rowid are counted and then averaged.

Datesliders are the most perfect invention since sliced bread (at least almost close to that)

Regards

[chfio]

Ah. Perhaps i could read more carefully! You saved the day. thanks!

[TomMartens]

You're welcome, it's been a pleasure to safe your day.

My girlfiend sometimes says: I wish you would listen more carefully, guess both of us share a common issue 🙂