Calculating daily average for a measure (with filters)

[Anonymous]

Here's the structure of my data:

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.

[v-frfei-msft · Accepted solution]

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

For more details, please check the pbix as attached.

Regards,

Frank

[Anonymous]

It worked perfectly! Thank you so much!

[AlB]

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]

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]

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

[Anonymous]

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

[AlB]

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