Add Trend Line to Line Chart with categorical X-Axis; Y-Axis is a measure.

[tmendoza]

Hello BI geniuses,

As the title says, I'm trying to add trend line to my line chart that has a categorical X-Axis. Further more, the Y-Axis value is a measure.

The measure is Book to Bill: SUM(NetSales)/SUM(NetRevenue)

The X-Axis is period based. There are 12 periods, each one representing a month. The first months of fiscal year 2026 would show as such: 202601, 202602, 202603, etc.

I've created a measure for the trend line that seems to work, but from 202512 to 202601 the trend line makes a sharp dip.

How do I make my trend line a single straight line like when my X-Axis is continuous.

Below is a capture of my current trend line when the X-Axis is continuous and categorical

I've provided the formula to my trend line measure below ('_Book to Bill' is what is in my line chart Y-Axis above; _Book to Bill in measure below = B2B in line chart Y-Axis above):

B2B Trend =

VAR Known =

    FILTER(

        SELECTCOLUMNS(

            ALLSELECTED(B2B[FiscalPeriod]),

            "Known[X]", B2B[FiscalPeriod],

            "Known[Y]",[_Book to Bill]

        ),

        AND(

            NOT (ISBLANK(Known[X])),

            NOT (ISBLANK(Known[Y]))

        )

    )

VAR Count_Items =

    COUNTROWS(Known)

VAR Sum_X =

    SUMX(Known, Known[X])

VAR Sum_X2 =

    SUMX(Known, Known[X]*Known[X])

VAR SUM_Y =

    SUMX(Known, Known[Y])

VAR Sum_XY =

    SUMX(Known, Known[X]*Known[Y])

VAR Average_X =

    AVERAGEX(Known, Known[X])

VAR Average_Y =

    AVERAGEX(Known, Known[Y])

VAR Slope =

    DIVIDE(

        Count_Items * Sum_XY - Sum_X * SUM_Y,

        Count_Items * Sum_X2 - Sum_X * Sum_X

    )

VAR Intercept =

    Average_Y - Slope * Average_X

RETURN

    SUMX(

        DISTINCT(B2B[FiscalPeriod]),

        Intercept + Slope * B2B[FiscalPeriod]

    )

Once again,

How do I make my trend line a single straight line like when my X-Axis is continuous.

Any help would be highly appreciated!

Thanks for your time!

-Tom

[v-sdhruv]

Hi @tmendoza ,

Thanks for sharing the update. This workaround will help other members facing the similar issue, resolve the query.
Thanks for reaching out on Microsoft Community Forum

[v-sdhruv]

Hi @tmendoza ,
Just wanted to check if you got a chance to review the suggestions provided and whether that helped you resolve your query?

Thank You

[tmendoza · Accepted solution]

Hey there, thanks for the response. I appreciate your insight. Unfortunately the suggestions didn't work out for me. What I ended up doing was translating the fiscal periods into 'calander end' dates. That way the x axis is forced to be a date and I can then set the x axis to be continuous rather than categorical. However, the x axis displays callendar months now rather than the fiscal periods (202601, 202602, 202603, etc.)

From what I've seen and tested, there is no solution to the issue that I was having, only a work around.

[v-sdhruv]

Hi @tmendoza ,
Just wanted to check if you got a chance to review the suggestions provided and whether that helped you resolve your query?

Thank You

[v-sdhruv]

Hi @tmendoza ,
Try these-

1. Convert Categorical to Numeric

   • Ensure fiscal periods (e.g., 202061) are treated as numeric in DAX.
   • This enables accurate slope and intercept calculations.

2. Use Continuous Axis

   • Under X-axis, change type from Categorical to Continuous.
   • This ensures a straight and reliable trend line

Hope this helps!

[tmendoza]

Hey there thanks for the response. The periods are currently formatted as whole numbers. Changing the X-axis from Categorical to continuous creates a large range of periods that don't actually exist. You can see it in the second picture in my post. There are only twelve periods, but when the axis is continuous, there are 100 periods, which just doesn't work.

[srlabhe]

Not sure about your ask here, but isnt the dip due to the data is that way ?

[tmendoza]

Thanks for responding to my post!

I updated the post to make it more clear.

The ask is,

how do I make my trend line a single straight line like when my X-Axis is continuous.

The dip is due to the large change in Sales and Revenue between fiscal years. I agree that the math is right.

However, visualy, I need a single straight line.