cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Calculating daily average for a measure (with filters)

Here's the structure of my data:

 

Screen Shot 2019-01-09 at 12.33.20 PM.png


My objective is to create a daily average of the Measure based on the filtered Date, Category, Subcategory1, and Subcategory2. The output will be a single valuethat goes in a card. The flters are as follows:

  • Date: Slicer
  • Category: Single-select
  • Subcategory1: Multiple-select
  • Subcategory2: Multiple-select

 

Logically, with the filters considered, the process is just to get the sum of Measure per day and get its average. I just don't know how to do this in DAX (or maybe another way?).

 

Can anyone help me with this? Thank you very much.

 

Side question: When asking a question, how do you upload your data in clean-looking tables? I see it in some of the questions but I don't know how.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculating daily average for a measure (with filters)

Hi @Anonymous,

 

To create a measure as below. If it doesn't meet your requirment, kindly share your excepted result to me.

 

daily average = CALCULATE(SUM(Table1[Measure]))/CALCULATE(DISTINCTCOUNT(Table1[Date]),ALLSELECTED(Table1))

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
LivioLanzo Super Contributor
Super Contributor

Re: Calculating daily average for a measure (with filters)

Hi burento try to copy paste the data directly from the spreadsheet. Are you also able to post the expected result you'd like to see? thx

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Super User
Super User

Re: Calculating daily average for a measure (with filters)

Hi @Anonymous

 

since you are already describing what needs to be done, I am not sure what the question is but you can place a measure like this in a card visual:

      MeasureAvg = AVERAGE(Table1[Measure])

applying the filters as you explain. Table1 is the name of your table as shown

 

Regarding how to paste table values, if you copy the table from Power BI you can usually paste it here in the text editor directly. It will be shown in a simple html table with text. That makes it easy for people willing to help to copy the data easily.

 

You can share the pbix by posting the URL to the file here. For that you will need to upload the file to sites like filedropper.com, tinyupload.com, etc. (no sign-in required) or to platforms like OneDrive, Dropbox, etc.

 

Anonymous
Not applicable

Re: Calculating daily average for a measure (with filters)

Thanks for the tips on copying/uploading data.

 

Re: question. Nope. The simple average won't work since it will take the average per row. I need the average per day. So the logic should be something like: apply filters then aggregate per day then take the average then put it in a card.

Anonymous
Not applicable

Re: Calculating daily average for a measure (with filters)

My expected result is a single column and when I take it's average, it will be the daily average of the measure (filters considered).

Super User
Super User

Re: Calculating daily average for a measure (with filters)

@Anonymous

I'm a bit confused as to want you are looking for. Maybe you can explain it through an illustrative example on your sample data? That would probably help clarify.

As said, please post the sample data also in table format (text) so that it can be readily copied.

Community Support Team
Community Support Team

Re: Calculating daily average for a measure (with filters)

Hi @Anonymous,

 

To create a measure as below. If it doesn't meet your requirment, kindly share your excepted result to me.

 

daily average = CALCULATE(SUM(Table1[Measure]))/CALCULATE(DISTINCTCOUNT(Table1[Date]),ALLSELECTED(Table1))

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Re: Calculating daily average for a measure (with filters)

It worked perfectly! Thank you so much!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 1,517 guests
Please welcome our newest community members: