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

      • 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?