Forum Discussion
Helper IIHelp 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 capacity). These are working fine with slicers on the page except the slicers for Office Location and Approval. Office Location and Approval are fields from the forecast table. I need help with the following:
- I want the measure for “AvgTop5Weeks” to show results for selected office locations and selected approval and
- I want the measure for “AvgTop5Weeks” to show results for weeks after a specified cutoff (say current week or next week)
Can you plz guide me?
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)
9 Replies
- Kaviraj11
Solution Sage
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]) - Kaviraj11
Can you explain with an example?
I would also recommend to create a DateDim or Calendar table.
Could you mark first solution as accepted?
arp2I want the AvgTop5Weeks measure to calculate the average of top 5 weeks which are after a specified date. Currently it is calculating the average of top 5 weeks among all weeks listed in the table. Example, let's say I want the average of Top 5 weeks which are after week 28th March.
- Kaviraj11here 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])