Forum Discussion

liucpaulpbi's avatar
liucpaulpbi
Frequent Visitor
9 years ago

Outlier behavior analysis with a given base line

Does Power BI provide a way for user to specify a base line / sample behavior of a time period in the past, and check outliers in a current time period for outlier behavior?  For instance, can I give sales numbers (or Dows, or class enrollment numbers) say in September 2015 as 'base line', and ask BI to search for outlier days against any month in 2017? The base line is used to tell a 'typical' or 'normal' trend while outliers are considered unusual behavior.

Need Help

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    What I would do in that case would be to create a measure for September 2015, something along the lines of:

     

    Baseline = CALCULATE(COUNTROWS(Enrollement),FILTER(Enrollement,YEAR(Enrollment[Date])=2015 && MONTH(Enrollment[Date])=9))

    Then, create another measure like:

     

    PercentDifference = COUNTROWS([Enrollment]) / [Baseline]

    Plot that in a visual and you will theoretically see outliers. Something along those lines.

     

    • liucpaulpbi's avatar
      liucpaulpbi
      Frequent Visitor
      9 years ago

      Thank you for quick advice. I think this direction should work conceptually. But as I am new to DAX, I need time to transform my usage data into this model, and validate the idea. Will get back soon. Thank you.

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi liucpaulpbi,

        I really understand you, you can post some sample table and list your requirement clearly, so that we can post specific solution.

        Thanks,
        Angelia

    • liucpaulpbi's avatar
      liucpaulpbi
      Frequent Visitor
      9 years ago

      I learned more about DAX and coded your suggestion. But it seems COUNTROWS([Enrollment]) / [Baseline] basically returns one number as a percentage. It is not a distribution outlier measurement.

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi liucpaulpbi,

        You don't want a percentage? What's a distribution outlier measurement, could you post an example.

        Best Regards,
        Angelia

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi liucpaulpbi,

    Please try to create measure as Greg_Deckler posted. From [PercentDifference], you will can see the difference to September 2015.

    You also can create a calculated column using the formula. If it is bigger than 100%, which displays it more behavior than September 2015.

    compare to September 2015 = CALCULATE(COUNTROWS(Enrollement),ALLEXCEPT(Enrollment,Enrollment[Month]))/ [Baseline]


    Don't hesitate to ask anything thing if you have other issue.

    Thanks,
    Angelia