cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Calculating daily average for a measure (with filters)

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.

1 ACCEPTED SOLUTION
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))`

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

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!

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

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.

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

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors