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 axis, I would like to add a Trendline.

 

I do not see the option at all when using the Line Chart or Clustered Column chart: 

 

Can anyone help? Thjis should be easy to do, its very frustrating!

 

Thanks

Need Help
Show and Tell

32 Replies

  • nleuck_101's avatar
    nleuck_101
    Continued Contributor
    3 years ago

    ArchStanton 
    Maybe try creating a new measure. Now for the return part you may want to change yours to COUNTX or SUMX depending on what your wanting your results to be. I used AVERAGEX for my example. LINEST AND LINESTX are new to Power BI from their Power BI Desktop update in February I believe. They can be used to create a simple linear regression line or trend line.

    Simple Linear Regression =

    VAR Known =
        FILTER(
            SELECTCOLUMNS(
                ALLSELECTED(Table[Date]),
                "Known[X]", Table[Date],
                "Known[Y]", Table[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]
        )
    • 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]
          )