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 

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

       

       

      • nleuck_101's avatar
        nleuck_101
        Continued Contributor
        3 years ago

        ArchStanton 
        You are missing a comma at the end

         

    • 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]
                    )