Forum Discussion
measure incorrectly repeating values for matrix visual
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.
Super UserAnonymous , 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
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.