measure incorrectly repeating values for matrix visual

[Anonymous]

I have a simple data model:

table: 'Dimension Project'

table: 'Dimension Employee Weekly Schedule'

table: 'Fact Sales'

I have a matrix visual with Rows:

'Dimension Project'[Proj ID]

    'Dimension Employee Weekly Schedule'[Employee ID]

For the Values, I want to include a measure that sums sales for the last 4 weeks.  However, on the page, there is a filter being applied that limits the timespan to the next 4 weeks (for other purposes).  So, I have implemented:

Sum of Sales - last 4 weeks =

CALCULATE(
    SUM('Fact Sales'[Amount])

    ,ALL('Dimension Employee Schedule')
    ,'Dimension Project'[Type] = "A"
    ,'Dimension Employee Schedule'[Week Number] IN {-1, -2, -3, -4}
)

This measure is returning the correct values at the 'Dimension Project'[Proj ID] level and the Total level.  However, it is repeating the value from the 'Dimension Project'[Proj ID] level when expanding into the 'Dimension Employee Weekly Schedule'[Employee ID] level.  This is incorrect behavior.  How can I correct this behavior?

For reference, I have tried the following, which no longer repeats the value down to the employee level.  But, now all employees are being returned per project, instead of only the ones that have a row in the 'Fact Sales' table for the given project.

CALCULATE(
    SUM('Fact Sales'[Amount])

    ,ALLEXCEPT(

        'Dimension Employee Schedule'

        ,'Dimension Employee Schedule'[Employee ID]

    )
    ,'Dimension Project'[Type] = "A"
    ,'Dimension Employee Schedule'[Week Number] IN {-1, -2, -3, -4}
)

[amitchandak]

@Anonymous , For the week, Create a week Rank in your week table and then try measure like. Week /date should be a separate table

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 4 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

As this might group data in one week, Refer this approch

https://www.youtube.com/watch?v=duMSovyosXE

[Anonymous · Accepted solution]

@amitchandak 

My problem isn't the filtering to the proper weeks.  My problem is that the measure value at the project level was repeating down to the employee level.  (As a minor note, my "employee" dimension table also serves as a "week" dimension table.  This is necessary because employee information is time-variant, e.g., an employee's weekly work hours changes by week because of holidays.)

However, it turns out that the second measure definition that I posted actually does give me the behavior that I want.  So, no help is needed after all.  Again, this measure is:

CALCULATE(
    SUM('Fact Sales'[Amount])

    ,ALLEXCEPT(

        'Dimension Employee Schedule'

        ,'Dimension Employee Schedule'[Employee ID]

    )
    ,'Dimension Project'[Type] = "A"
    ,'Dimension Employee Schedule'[Week Number] IN {-1, -2, -3, -4}
)

In testing, it turns out that another measure (not the measure above) on my matrix visual was forcing all employees to return for each project.  The measure definition above wasn't causing it.