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

## Need a formula to produce overall average per week of month

It's Monday and the answer is probably as simple as 1 + 1 = 5 (ha ha) but I can't figure it out:

In my Date table, I have a column with the Week of the Month (according to how we break them down) for each day, I have the total number of months for which I have data for each of those weeks and sum count of all the tickets for each of those week blocks:

I need a formula that will give me the AVERAGE for each of those week blocks vs. the Sum of them.  So, for example, instead of seeing 42,298 for W1, I'd want to see 7,049.  These will ultimately be put on line axis of a cluster chart along with other data (the counts for each week block of the current month and previous month so we can compare how the last 2 months compare with the cumulative average for all months).

Note that depending on the day of the month, not every week-block will be based on the same number of months (see W4).

What totally obvious (probably) formula would I use to produce that?

If you are willing to go one step further, ultimately what I'll need are the weekly block average that will still respect any filters/slicers applied to it EXCEPT my selected date range filter.  The other data that is in the clustered chart must still respect the date filters applied...just the line for this data should ignore that (so I can't use the Edit Interactions on it).

1 ACCEPTED SOLUTION
Anonymous
Not applicable

This goes to show how stupid you can be when you don't get enough sleep.  How about the count / count of months????  DUH!

Anonymous
Not applicable

This goes to show how stupid you can be when you don't get enough sleep.  How about the count / count of months????  DUH!

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 - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors