Data Analysis Expression (DAX)

1. Find the typical YoY growth?

Concepts touched: most frequent value, previous year

We solve this problem by solving two smaller problems. First, we will specify what ''typical'' means and will implement it. Secondly we will implement the year over year (YoY) growth calculation.

Think about it for a second, then read on, but, if I was in your place, who might be just starting out with DAX, I would do this.

  1. Think how this could be accomplished and take an informed guess - this typically involves guessing the keywords and...
  2. Locate the canon - DAX reference(authoritative collection of text) and search for those words first, then, do a quick scan through those pages.
  3. Use your experience and ask yourself, whether you are facing a lack of knowledge and where the gap is? Then take a step back and read at a higher conceptual level keeping the identified gaps in your mind, and understand the interconnectedness of the concepts, as if you are reading a map before heading out. Remember quality over quantity, is the key when trying something new, so stick to the canon and repeat the process. This is because often the authorities in the subjects are the authors of official canons.
  4. Do trial and error in an environment that makes each iteration frictionless - for DAX in Power BI, it will be Power BI Desktop.

A possible solution

Calculate the metric for the same period but last year:
We could find the dates in the range and by taking the maximum and minimum and subtracting one from the year componnent of all those dates, we could arrive at the previous year ... Yes we could do that, but nobody does that, as we have a built-in function SAMEPERIODLASTYEAR (see code). It asks for a date column, which is any column that has all the date values from the very start to the end of time, like a calendar.
For this to work this calendar like table, should be able to filter the table which contain the data. In otherwords, a relationship must be established between them (see a possible data model at dax.do where the calendar table Date is connected to Sales table using Order Date column).
Most frequent value in a table:
We are choosing to define "typical" to mean the most frequently occuring value. If you search DAX reference for MOSTFREQUENT you will not find any built in function. Which means we have to built it ourselves.
MOSTFREQUENT can be found by counting the number of times each thing appears in the data and finding the thing with the most count. Which means this subproblem has two subproblems within it. First to count each thing (call it COUNTOF), secondly to use that count, to find the thing with maximum count (call it MAXIMUMVALUEISFOR).
In DAX a COUNTOF can be implemented by using GROUPBY, CURRENTGROUPand COUNTX. (see code)
  1. Counting Each Thing[COUNTOF]
    For doing count of values in a column we have COUNT, but that counts the entire table. So if we could partition the table based on the thing that we what to count, and then do the count of each of those partitions, we may be able to accomplish it. This can be done by GROUPBY(function parameters), then we use two built-in functions, namely:
    1. X Function
      This is were we specify what we will do. If we are counting the things. Then use COUNTX, if we are summing up, use SUMX, if we are finding average use AVGX etc.
    2. CURRENTGROUP
      This refers to a partitions created by GROUPBY when it is grouping the various rows in a table based on a column value.
  2. Get max valued thing[MAXIMUMVALUEISFOR]
    To find the row with the top value from the table created by COUNTOF, We have TOPN.

2. Top Performing Category Based on A Calculation

ALLSELECTED() vs just column name in GROUPBY()