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