Hi ,
We are working on Power BI reports for a client with below requirement:
Requirement:
We want to add a reference line in a bar chart below which is calculated as:
Average of TOP 10 weeks in the last 52 weeks (from today) for the selected Station and Circle (Station and Circle are slicers on the report).
- Every week this average will get updated
- Summarize data at Week level for All the values selected in slicers for Station and Circle and then calculate TOP 10 weeks by TPH, then take average of these 10 values.
- Plot this average value as a reference line in the chart which shows Weekly data for TPH measure.
Logic:
Step 1: Calculate a table for last 52 weeks
Last 52 Weeks Table = SELECTCOLUMNS (
FILTER ('Sheet1','Sheet1'[WEEKDIFF]>=1 && 'Sheet1'[WEEKDIFF] <=52),
"ACT HOURS",Sheet1[Actual Hrs],
"TONS PROD", Sheet1[Tons Produced],
"Week Number", Sheet1[Week Number],
"Year", Sheet1[Year],
"WeekEndDate", Sheet1[WeekEndDate],
"WEEKDIFF", Sheet1[WEEKDIFF])
Step 2: Add RANK Column for WeekYear based on metric TPH in the table created in Step 1.
Step 3: Create table for storing TOP 10 Weeks based on RANK Column created in Step 2.
Step 4: Calculate the AVERAGE of the 10 records returned in Step 3.
We are stuck on Step 2. Even though the calculation is on the new table its returning records older than 52 weeks. Also its important that the Average calculation should be dynamic , so the TOP 10 records should change as per slicer selections.
If anyone can help on the flow of logic and DAX functions it will be great, thanks.
