DAX Trend line - need two lines in one formula

[JenWilson]

The Dax trend line formula I am using is working great, however, when visualized within a stacked column chart with multilple X axis items (Sorted first by date and second by Dept), it appears jagged (see screen shot 1). If I flip the drill down order of the X axis (putting Dept first and Date second), I get a better visual (still connected, but one section for Dept A and one for Dept B - see screen shot 2). The requirements I've been given would best be represented by the first screen shot.  With that...

Is there anyway that I can modify the formula below so that this one formula would produce two trendlines for each department? (The "Known[Z]", [Department] portion of the formula below).

Job Transition Time Trend Dec. Hrs Measure Eagle 1 & 2 =

VAR Known =

Filter(

SELECTCOLUMNS(

ALLSELECTED ('Calendar'[Date]),

"Known[X]", 'Calendar'[Date],

"Known[Y]", [TotalHours],

"Known[Z]", [Department]

),

AND(AND(NOT(ISBLANK(Known[X])), NOT(ISBLANK(Known[Y]))),Known[Z]))

VAR Count_Items = COUNTROWS(Known)

VAR Sum_X = SUMX(Known, Known[X])

VAR Sum_X2 = SUMX(Known, Known[X]^2)

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^2)

VAR Intercept = Average_Y - Slope * Average_X

Return SUMX(DISTINCT(Calendar[Date]), Intercept + Slope * Calendar[Date])

[Anonymous]

Thank you lbendlin

Hi, @JenWilson 

This issue usually occurs when you use Column Legend as shown in the image below:

To solve this problem, you can create a measure for each of your two departments and then apply it to the chart:

This will connect all your dots into one line. I've provided the PBIX file used this time below.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JenWilson]

@Anonymous 
This isn't working. My "dots" represent data points of two trend line measures (one for each dept) and the formula you provided, doesn't allow the use measures in the calculation. I believe the problem that I am having is that the X axis consits of the date and then the department so it only makes sense that these two measures only show dots (as each measures needs to skip over the department that is not associated with it). 
If I use a single measure for both departments, I get a solid line, but it connects each data point for each department which causes the jagged line. P.S. I have no content in the column legend field for my stacked bar chart. Let me know if you have any other ideas. Thank you! 

[Anonymous]

Hi, @JenWilson 

After I think again, if you're in a situation where one of the measure only returns a value in a particular department, and the other departments don't return any value, at the moment as Super says: it's trying to do too many things, and it can't do it at the moment.

If you need such a need, you can propose the idea in the ideas forum:

Ideas (microsoft.com)

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Is there anyway that I can modify the formula below so that this one formula would produce two trendlines

No. What made you not consider using two measures?

[JenWilson]

@lbendlin , I have individual measures also, but the charting only gives me dots vs. a nice line for each department as shown below in the first screen shot. I suspect this is because of the sort order of the two X axis which is Date and Dept. If I reverse the sort order to have Dept then Date, I get the solid line. See the second screen shot. But again, the requirement is to have the data grouped by each date and dept together. So for the individual measures, is there a way to show these dots as a solid line?  

[lbendlin]

But again, the requirement is to have the data grouped by each date and dept together.

I find your second option much easier to understand and analyze.  Push back on the requirement, it is trying to do too much.

[JenWilson]

@lbendlin 
I get why the requirements are being asked for. The owner of the report is looking to see trends not only as a whole but comparing one department to the next. The screen shots that I provided were only a few days of the data. So when the owner of the report wants to compare say month over month, seperating the departments wouldn't help the user.  Thanks for trying!