To calculate the average efficiency based on consecutive days, excluding Sundays, and grouped by the specified columns in Power BI, follow these steps. This solution requires DAX formulas to handle the grouping and averaging for only consecutive calendar days.
Step 1: Import and Prepare Data
1. Import your data into Power BI.
2. Ensure the Effective Date column is formatted as a date data type.
Step 2: Add a Column to Determine Consecutive Days
1. Create an Index Column to track row numbers. This will help in identifying consecutive days.
- Go to Transform Data > Add Column > Index Column > From 1.
2. Create a Calculated Column for Consecutive Days Check:
Use the following DAX formula to identify if each row is a consecutive day (excluding Sundays) from the previous row, based on Plant Code, PRODUCT_TYPE, CHASSIS_CODE, SUB CHASSIS, Work_Center, and Style:
DAX
IsConsecutive =
IF(
TableName[Plant Code] = EARLIER(TableName[Plant Code]) &&
TableName[PRODUCT_TYPE] = EARLIER(TableName[PRODUCT_TYPE]) &&
TableName[CHASSIS_CODE] = EARLIER(TableName[CHASSIS_CODE]) &&
TableName[SUB CHASSIS] = EARLIER(TableName[SUB CHASSIS]) &&
TableName[Work_Center] = EARLIER(TableName[Work_Center]) &&
TableName[Style] = EARLIER(TableName[Style]) &&
WEEKDAY(TableName[Effective Date], 2) <> 7 &&
DATEDIFF(EARLIER(TableName[Effective Date]), TableName[Effective Date], DAY) = 1,
1,
0
)
Replace `TableName` with your actual table name. This column (`IsConsecutive`) will have a value of `1` if the day is consecutive (excluding Sundays) and `0` otherwise.
Step 3: Group Data by Consecutive Blocks
1. Create a Calculated Column for Grouping Consecutive Blocks:
Create another calculated column to assign a unique identifier to each block of consecutive days.
DAX
ConsecutiveGroup =
SUMX(
FILTER(
TableName,
TableName[Index] <= EARLIER(TableName[Index]) &&
TableName[IsConsecutive] = 1
),
1
)
This column (`ConsecutiveGroup`) will assign a unique number to each group of consecutive days for each combination of `Plant Code`, `PRODUCT_TYPE`, `CHASSIS_CODE`, `SUB CHASSIS`, `Work_Center`, and `Style`.
Step 4: Calculate the Average Efficiency for Consecutive Days
1. Create a Measure for Average Efficiency:
Use the following DAX measure to calculate the average `Avg Actual Efficiency` for each group of consecutive days.
DAX
AvgEfficiencyConsecutive =
CALCULATE(
AVERAGE(TableName[Avg Actual Efficiency]),
ALLEXCEPT(
TableName,
TableName[Plant Code],
TableName[PRODUCT_TYPE],
TableName[CHASSIS_CODE],
TableName[SUB CHASSIS],
TableName[Work_Center],
TableName[Style],
TableName[ConsecutiveGroup]
)
)
This measure will calculate the average efficiency only for consecutive days within each combination of the specified categories.
Step 5: Visualize the Results
1. Use a Table or Matrix Visual in Power BI to display the data.
2. Add Plant Code, PRODUCT_TYPE, CHASSIS_CODE, SUB CHASSIS, Work_Center, Style, and ConsecutiveGroup as rows, and use the `AvgEfficiencyConsecutive` measure as values.
3. This will show the average efficiency for each consecutive run of days for each unique combination of fields, excluding Sundays.
This setup will ensure that only consecutive calendar days are considered for the efficiency average, and any gaps (non-consecutive days) will separate the calculation into different groups. Let me know if you need further customization!
Hi @charithaa
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks,
Farhan
