Power BI折线图技术问询：X轴显示Week of year，基于每日员工Commission平均值绘图

Absolutely, this requirement is totally achievable in Power BI! Let's walk through the step-by-step solution to build exactly the line chart you want—with X-axis showing "Week of year", daily granularity (≈365 data points), and each point representing the average commission of your two employees as needed.

Step 1: Prep Your Data Model #

First, make sure your data is structured correctly:

• Confirm the Date field is formatted as a Date/Time type in Power BI.
• Verify your Week of year mapping is properly linked to each date (you mentioned you already have a lookup table for this, so that’s good to go).
• Add a calculated column for Year if you don’t have one already (to separate 2020 and 2021 lines):

  Year = YEAR('YourDataTable'[Date])
  

Step 2: Create the Right Measure #

Pick the measure that aligns with your exact needs:

Option 1: Daily Average Commission (per date) #

This calculates the average commission of Alice and Bob for each individual date (each data point ties to a specific day):

Daily Avg Commission = 
CALCULATE(
    AVERAGE('YourDataTable'[Commission]),
    ALLEXCEPT('YourDataTable', 'YourDataTable'[Date])
)

Option 2: Weekly Average Commission (displayed on daily points) #

If you want every date in a week to show the average commission for that entire week (all days in the same year-week group share the same value), use this:

Weekly Avg by Date = 
VAR CurrentWeek = SELECTEDVALUE('YourDataTable'[Week of year])
VAR CurrentYear = SELECTEDVALUE('YourDataTable'[Year])
RETURN
CALCULATE(
    AVERAGE('YourDataTable'[Commission]),
    FILTER(
        ALL('YourDataTable'),
        'YourDataTable'[Week of year] = CurrentWeek && 'YourDataTable'[Year] = CurrentYear
    )
)

Step 3: Configure the Line Chart #

Now build the chart with these settings:

1. Add a Line Chart visual to your report.
2. Legend: Drag the Year column into this field (this creates separate lines for 2020 and 2021).
3. Axis (X-axis):
   • First drag Week of year into the Axis field.
   • Then drag Date into the same Axis field (place it below Week of year). This tells Power BI to retain daily granularity while grouping by week for display.
   • Go to the Format pane > X-axis > Labels > Set "Label source" to Week of year. Now your X-axis will show week numbers instead of dates, but keep all daily data points.
4. Values: Drag the measure you created (either Daily Avg Commission or Weekly Avg by Date) into this field.

Step 4: Tweak for Clarity #

• Ensure your Date column is sorted in ascending order (right-click the column > Sort by Column > select Date). This keeps your line chart points ordered chronologically.
• If X-axis labels overlap, adjust the "Label density" in the Format pane to space them out better.

That’s it! You’ll now have a line chart with X-axis showing week numbers, daily granularity data points, and separate lines for each year—exactly what you were aiming for.

内容的提问来源于stack exchange，提问作者J. Mini