Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
arp2
Helper II
Helper II

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 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:

  1. I want the measure for “AvgTop5Weeks” to show results for selected office locations and selected approval and
  2. 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?

DummyMar1 R0.pbix

 

5 ACCEPTED SOLUTIONS
Kaviraj11
Responsive Resident
Responsive Resident

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])

View solution in original post

Thanks @Kaviraj11 would you be able to guide on the 2nd point?

View solution in original post

Kaviraj11
Responsive Resident
Responsive Resident

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 result
AvgTop5Weeks =
AVERAGEX(TOPN(5,FILTER(SUMMARIZE(ALLSELECTED(ForecastTable),ForecastTable[Week],"TotWeek",[SumFcstWeek]
),ForecastTable[Week]<=TODAY()),[TotWeek],DESC),[TotWeek])

View solution in original post

Kaviraj11
Responsive Resident
Responsive Resident

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)

View solution in original post

Thanks @Kaviraj11 this is working as required.....I wanted dates after today so I just changed the "less than" to greater than.

View solution in original post

9 REPLIES 9
Kaviraj11
Responsive Resident
Responsive Resident

Can you explain with an example? 

I would also recommend to create a DateDim or Calendar table. 

Could you mark first solution as accepted?

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

Kaviraj11
Responsive Resident
Responsive Resident

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 result
AvgTop5Weeks =
AVERAGEX(TOPN(5,FILTER(SUMMARIZE(ALLSELECTED(ForecastTable),ForecastTable[Week],"TotWeek",[SumFcstWeek]
),ForecastTable[Week]<=TODAY()),[TotWeek],DESC),[TotWeek])

In above, where and how do I specify the date after which I want the measure to calculate the average?

Kaviraj11
Responsive Resident
Responsive Resident

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)

Thanks @Kaviraj11 this is working as required.....I wanted dates after today so I just changed the "less than" to greater than.

@Kaviraj11 plz see above.....kindly let me know if this is not clear yet

Kaviraj11
Responsive Resident
Responsive Resident

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])

Thanks @Kaviraj11 would you be able to guide on the 2nd point?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.