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

[hansreivers]

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? 

[hansreivers · Accepted solution]

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

B

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 🙂

[dharmendars007]

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

LinkedIN 

[mark_endicott]

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