cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
burento Frequent Visitor
Frequent Visitor

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 @burento,

 

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.
7 REPLIES 7
Super User
Super User

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 @burento

 

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.

 

burento Frequent Visitor
Frequent Visitor

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.

burento Frequent Visitor
Frequent Visitor

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)

@burento

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 @burento,

 

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.
burento Frequent Visitor
Frequent Visitor

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

It worked perfectly! Thank you so much!