topic Average / Divide - changes with drill down. I don't want that in DAX Commands and Tips

Average / Divide - changes with drill down. I don't want that

hansreivers — Mon, 16 Dec 2024 15:06:55 GMT

Hi all,

I cannot get the right answer;

I've a simple table with per day - week - yyyy-mm - year a number of orders.

I want to have an average per week for the years regardless of the drilldown level.

Can anyone help me?

Re: Average / Divide - changes with drill down. I don't want that

mark_endicott — Mon, 16 Dec 2024 15:13:22 GMT

Please send a sample of how your data is stored within the tables we need to use.

If you need any more guidance on how to get answers quickly please see this post: https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/m-p/1626726#M32906

Re: Average / Divide - changes with drill down. I don't want that

dharmendars007 — Tue, 17 Dec 2024 05:38:03 GMT

Hello hansreivers ,

Please try creating a measure with week in year.

ALLEXCEPT ensures the measure filters only the Year column and removes any other drilldown context

Weeks in Year =
CALCULATE(
DISTINCTCOUNT('Calendar'[WeekNum]),
ALLEXCEPT('Calendar', 'Calendar'[Year]))

Divide it by total orders.
Avg Orders per Week =
DIVIDE([Total Orders],[Weeks in Year])

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

Re: Average / Divide - changes with drill down. I don't want that

hansreivers — Tue, 17 Dec 2024 10:45:55 GMT

Hi All,

I have come to the solution myself (with help from the forums)!

Solution for me is;

AVG total per week-year =

VAR A = MAX('Calendar'[Year])

VAR B = CALCULATE(

DIVIDE([total], [Last week of year]),

ALL ('Calendar'), 'Calendar'[Year] = A)

RETURN

The last week of year, looks to the weeknumber of the specific year. When this is 30, the number will be divided by 30 (for the "running" year).

Thanks for your help, and sorry for the "bad" explanation from my side 🙂