Forum Discussion
Solved
Help with slicer measure
Team, I have three tables showing weekly forecast, capacity, and plan by discipline, Project, and Grouping. I've created measures for the Average of the Top 5 weeks (for Forecast) and Max of Capacity...
For the 1st Problem - you can change the following Measure to
SumFcstWeek = CALCULATE(SUM(ForecastTable[Forecast Hours]))AvgTop5Weeks = AVERAGEX(TOPN(5,SUMMARIZE(ALLSELECTED(ForecastTable),ForecastTable[Week],"TotWeek",[SumFcstWeek]),[TotWeek],DESC),[TotWeek])- here is the DAX below:you can use DateAdd function as well, to verify the result, you can create a calcualted column anf using the summarize part of the function to validate your resultAvgTop5Weeks =AVERAGEX(TOPN(5,FILTER(SUMMARIZE(ALLSELECTED(ForecastTable),ForecastTable[Week],"TotWeek",[SumFcstWeek]),ForecastTable[Week]<=TODAY()),[TotWeek],DESC),[TotWeek])
ForecastTable[Week]<=TODAY())
above expression will consider all records that are less than equal to today
If you want to do it by DAY,Month,YEAR, you can use
ForecastTable[Week]>=DATESINPERIOD(ForecastTable[Week], TODAY(), 11, DAY)
For the 1st Problem - you can change the following Measure to
SumFcstWeek = CALCULATE(SUM(ForecastTable[Forecast Hours]))
AvgTop5Weeks = AVERAGEX(TOPN(5,SUMMARIZE(ALLSELECTED(ForecastTable),ForecastTable[Week],"TotWeek",[SumFcstWeek]),[TotWeek],DESC),[TotWeek])