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.

     

ArchStanton's avatar
ArchStanton
Power Participant
3 years ago

Thanks!

Apologies for the daft sounding question, where should I be writing this measure?

I have a customised Date Calendar called Date 2 which is linked to a Table called Cases (thats what I'm counting the no of text IDs of).

 

I'm trying to substitute your code with my data fields, can you help me with this?

 

Simple Linear Regression =

VAR Known =
    FILTER(
        SELECTCOLUMNS(
            ALLSELECTED(Date2[Date]),
            "Known[X]", Date2[Date],
            "Known[Y]", Cases[DistinctCount measure]
        ),
        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(Table[Date]),
        Intercept + Slope * Table[Date]
    )
nleuck_101's avatar
nleuck_101
Continued Contributor
3 years ago

ArchStanton 

The measure should be in your Cases table.

  • ArchStanton's avatar
    ArchStanton
    Power Participant
    3 years ago

    Hi Sorry about this, my DISTINCTCOUNT of IDs is derived from the 'Cases' table but I'm

    struggling to substitute your Values e.g. Known [X] & [Y] etc with my actual table names

     

    My Date table is called Date2.

     

    Can you tell what belongs where please?

    Much appreciated

    • 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

        I can see Date in your example so I'm assuming that should be my Date2 calendar. 

        I'm struggling with the ALLSELECTED attributes, I've tried various combinations and nothing works