Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
3 years ago
Solved

Trendline needed

I have a simple linechart with MonthName on the x-axis (this is derived from my own custom built calendar going from Apr to Mar as per Financial Year) and a DISTINCTCOUNT Measure of IDs for the Y axi...
Need Help
Show and Tell
  • nleuck_101's avatar
    nleuck_101
    3 years ago

    ArchStanton 

    I would think they would be the same line.

     

nleuck_101's avatar
nleuck_101
Continued Contributor
3 years ago

ArchStanton 
Your Known[X] & [Y] become the new names of the fields. You don't need to substitute anything else. For example:

VAR SlopeIntercept =
    LINESTX(Known, Known[Y], Known[X])
You do not need to replace Known[Y] or Known[X] with anything. You've already identified them earlier in the VAR Known.
Does that make sense? Are you receiving an error?
ArchStanton's avatar
ArchStanton
Power Participant
3 years ago
VAR Known =
    FILTER(
        SELECTCOLUMNS(
            ALLSELECTED(Date2[Date]),
            "Known[X]", Date2[Date]
            "Known[Y]", [DISTINCT COUNT & SUM]
        ),
        AND(
            NOT(ISBLANK(Known[X])),
            NOT(ISBLANK(Known[Y]))
        )
    )
                VAR SlopeIntercept =
                    LINESTX(Known, Known[Y], Known[X])
                VAR Slope =
                    SELECTCOLUMNS(SlopeIntercept, "Slope", [Slope1])
                VAR Intercept =
                    SELECTCOLUMNS(SlopeIntercept, "Intercept", [Intercept])
                RETURN
                    AVERAGEX(
                        DISTINCT(Date2[Date]
                        Intercept + Slope * Table[Date]
                    )