Types of calculations in Power BI

You know how in Excel you can create columns of calculations with whatever raw inputs you have? Well, you can do the same thing in Power BI, but I will say it is a bit harder to do. There is a learning curve – for me it’s a big one.

From what I can tell, there is generally 3 methods of creating your calculations and the one I choose is whatever method I happen upon that works. I experiment, I tinker, I Google my way to a solution that solves most closely to what I need. I’m going to explicate on what I’ve learned in the past year but bear in mind I am no expert. There may be a better approach than what I’ve chosen, but I haven’t figured out how to make it work. Right now, I’m just throwing stuff at the wall to see what sticks.

A. Measures – take your measure

The easiest are the Quick Measure or the New Measure. These are found under Home menu.

I regard them like variables but they can hold either a static variable such as a single number or a formula. They become part of your list of fields in the Fields pane and are marked as measures with an icon of a calculator.

Whenever I need to do some kind of calculation, I first look at the Quick Measure to see if Power BI can do the heavy listing for me; however, sometimes quick measures do not work. Here’s how the Quick Measure creation box look after clicking on the Quick Measure button:

You can see that Power BI can create quite a few common calculation or statistics so it’s worth trying that route first. Most of the time the Quick Measure works, but every once in a great while I find that the calculations fail and thus, I turn to New Measure button where I have to write the formula itself. Creating the measure requires knowing a little bit about the DAX language (I think the M language is used in the Power query that pulls in the data from your outside sources).

There is a Quick Measure for moving averages, or Power BI calls it rolling average, but I never could make it work properly, so I had to create the measure itself. Here’s an example formula:

deathInc_Moving_Ave_Covid_tracking = 
CALCULATE (
    AVERAGEX (FILTER('Covid Tracking Historical','Covid Tracking Historical'[Attribute]="deathIncrease"),'Covid Tracking Historical'[Value]),
    DATESINPERIOD (
        'Covid Tracking Historical'[Date],
        LASTDATE ( 'Covid Tracking Historical'[Date]),
        -('Non-repatriated COVID-19 cases in the US by state (  vte  )'[Day Variable]),
        DAY
    )
)

This particular example is moving averages for deaths using data in the Covid Tracking Project. Here I’m using the 7-day rolling average that is incorporated in the variable “Day Variable”. This measure is the one I use for states and I have another one for US overall. Later, I’ll explain why I have two moving averages variable – maybe in another post.

B. Columns – adding columns in Power Query

Sometimes my Google answer does not tell me to create a measure but a column in Power Query, so I go there – wherever my answer takes me.

As far as I can tell, there are five ways to add a column.

1. The easiest is to try to do it by example and that is where I usually start first.

(Ignore the comment about the preview being 111 days old.)

Of course, sometimes that doesn’t work.

2. I sometimes use the custom column which requires writing formulas in M language. An example is a column showing the only the month of the date (Date-Month variable). I wanted to do a chart showing the month-by-month averages for 2020, whether cases or deaths, and creating a column specifying the month was what I found. Here’s an example of what I mean:

When you click on the Custom Column icon, you will get the following pop-up box:

For the Date-Month column, here’s the formula used to create that column:

= Date.Month([Date])

3. The “Invoke custom function” column I’ve used only once and that was long ago, so I won’t delve into this at all. This method sounds like creating a “function” you can then use in multiple areas to perform work you need done, kind of like creating a function in Excel (which I’ve only done once).

4. I’ve never used the “Conditional column” so I won’t go into this method either.

5. The “Index column” I use all the time. I use this when I receive data as a cumulative number daily rather than that day’s number. So, when I get a new set of data for the day, the cases or death counts come in as a cumulative number but I really want that day’s number, not a cumulative. To get that day’s count or sum, I have to subtract the newest cumulative data from yesterday’s cumulative number, and the “Index” columns help me achieve that.

Here’s an example:

In this example, Alaska’s case count for 1/2/2021 was 801 and about 7 days (roughly 12/24/2020) ago it was 268. The index number (“this week index”) for 1/2/2021 is 284 and the index number (“this week index”) for 12/24/2020 is 268. What Power BI does is map “this week index” to “last week index” of 284 and pull in the case count of 268 next to 801 so I could do some kind of difference calculation. To achieve these index columns, one was set to initiate at zero and the other at 7 to kind of represent 7 days later.

This is one use of the Index column; there may be others that I haven’t thought of.

C. Columns – adding columns in the charting area

The last option is creating columns in the workspace area where you create tables and charts. The addition of a new column is found under Modeling:

This methodology came up when I wanted to extend the monthly average bar graph into 2021, but the resulting graphs was not at all what I wanted. Image 5 bar graph showed only 2020 data:

Adding 2021 led to these various permutations, depending on how I set the “drilldown” in the graph:

What I really wanted was like the following:

The only method that stuck on the wall was creating a new column in the main chart area – it’s possible that I could do it under the Power Query but that was my Googled answer. I created a new column called Date-Month-Year which, to my eyes became a new function/variable in the Field pane.

Again, through the Modeling menu, I add the New Column and I get the following formula bar to create my formula:

Here’s the formula I used to create the Date-Month-Year variable (I really don’t regard this as a new column despite the nomenclature):

The thing about this “New Column” is it doesn’t create a new column that you can see in the Power Query editor; hence, that is why I don’t regard it as a new column but rather a new variable.

When do I use each of these three methodologies?

At this point I’m just using whatever sticks. Like I said earlier, I start off with the Quick Measure because that is the easiest and then work my way from there. There is supposed to be some efficiency differences in creating a new column in Power Query versus creating a new column (which to my eye is really a field) in the modeling/charting area, one that will speed up the updating and calculating process. But I’m going to have to revisit that topic later on.

Baby steps, baby steps.