cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Daily average

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 possibleSmiley Happy

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Daily average

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.

Number of Rows per Day.png

 

Hope this is what you're looking for.

 

Regards



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

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

Re: Daily average

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.

Number of Rows per Day.png

 

Hope this is what you're looking for.

 

Regards



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

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Highlighted
Regular Visitor

Re: Daily average

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


slide1.PNGslide2.PNGslide3.PNG

 

 

 

 

 

 

 

Highlighted
Super User III
Super User III

Re: Daily average

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



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

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
Regular Visitor

Re: Daily average

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

 

Highlighted
Super User III
Super User III

Re: Daily average

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 🙂



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

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors