Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
v-frfei-msft
Community Support
Community Support

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 others find it more quickly.

View solution in original post

7 REPLIES 7
v-frfei-msft
Community Support
Community Support

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 others find it more quickly.
Anonymous
Not applicable

It worked perfectly! Thank you so much!

AlB
Super User
Super User

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

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.

LivioLanzo
Solution Sage
Solution Sage

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!  

Anonymous
Not applicable

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).

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.