Posted on by moocsumo

Module 5: Creating and Formatting Measures

Module 5: Creating and Formatting Measures

“Creating and Formatting Measures … Using Advanced DAX Functions … Creating Measures using Advanced DAX Functions”
(Source URL)

Summaries

  • Module 5 > Creating and Formatting Measures > Lecture
  • Module 5 > Using Advanced DAX Functions > Lecture

Module 5 > Creating and Formatting Measures > Lecture

  • Remember we have this sales table that doesn’t have the revenue or the cost calculated.
  • I am going to again calculate it as a calculated column the revenue.
  • Once I have such a column, remember this is a calculated column it uses a context we call the row context so it can use all the other columns in the same row.
  • Based on regular columns or calculated columns, I can create measures.
  • Once I have revenue, let’s say, I can create tote revenue column equals sum of revenue close parenthesis so now I have myself a measure that I would be able to use in the pivot.
  • I want to show you an alternative way to calculate actually the same number.
  • This is a way that will actually not even require me to add this calculated column to do the multiplication for Each row, because what we do here is, for each row I multiply the order quantity with the list price.
  • I can actually have a function, a measure that will do this multiply for each row in just one shot without requiring a special calculated column.
  • So let’s do TotRevenue take 2 as a measure, column, equal, and now I will use instead of the sum function, the sum function is a simple function that’s very similar to the sum function Excel, just takes all the values and sums them.
  • This expression is very similar to the one in which we just did on in the calculated column.
  • Because it actually goes in each row and calculates exactly the same thing.
  • You can see that these two are actually returning the same value.
  • You will see that there is actually no need to use both because they are exactly the same.
  • Only one is using a calculated column and summarizing the results of the calculated column.
  • While the other is doing this multiplication while it’s being calculated as a measure.
  • So if I add to it any, let’s say country you see that this really just repeats itself.
  • It is exactly as the second one, only calculated in two different ways.
  • So once I am in the field list of the pivot table, first of all, I can see the measures right here with a special icon designating them as measures.
  • Its purpose is to give me the total for all countries regardless of the context.
  • So if I use it for Australia it will give me all countries.
  • If I use it for Canada it will give me all countries.
  • I will not use it directly in the pivot but I would actually use it in some further calculations.
  • So in order to do that I will use I will introduce for the first time a function which is maybe the mother of all functions in DAX called calculate.
  • So the calculate as you can see expect two parameters, the first is an expression.
  • I can give it an expression that I already calculated, the total revenue.
  • I have two options to actually calculate the same, as we saw.
  • As I said in this example, I want to actually calculate the sales for all countries, regardless of which country is currently being filtered by the context.
  • We’ll just say, forget about what’s in the context, for this specific column of country.
  • So when I add it to the pivot you see is that it doesn’t matter what is in the row for country.
  • It just is actually does not respond to the change between Australia, Canada, and so on, between the countries.
  • Now, if I add this one to the I forgot to actually format it.
  • So let’s say that this is a number and it’s actually a percent.
  • So now we see for each country, how the percent for this country is from the total.
  • We divided the actual revenue, which is calculated by the context which include the country with a measure that ignores the country.
  • For the grand total, it gives us 100% and it’s true that you can actually do something like that in the context of the pivot to show as the percent from the total but by doing this with DAX formulas, we can get far beyond what we can do actually by using just the features of the pivot.
  • You will see later on examples in which the calculate function will become more and more complex and do more and more sophisticated stuff.
  • In this part we actually covered the beginning of the calculate function, we saw the use of the sumx function, as a representative for the family of the x functions, with, together with and so on and we started to feel the power of the DAX language, more in the second part.

Module 5 > Using Advanced DAX Functions > Lecture

  • I can pick the last year or I can pick a previous year.
  • OnceI pick this year, first of all on the left side I see a chart with the sales of all the years.
  • These sales do not, this chart here does not work with the slicer.
  • So this chart is not connected to the slicer, so we chose all the years.
  • It has some source for getting the competitor’s total sales and it can compare the market share.
  • Calculate the market share between their sales and others.
  • The vendor here it just filters on the sales which I call our sales.
  • The main chart here is showing for the year that I selected, month by month, the difference between this year and the year before.
  • There is a sentence here which I also built using formulas, I will show it to you in a moment that says the total between 2013 and 2015, the sales shrank 5%. If I go here, you’ll see there the sales between 2012 and 2013 grew 8% total.
  • For each month you’ll see that there are some months in which the growth like here for March, there’s a negative.
  • So there’s less sales in March of 2013 than in March of 2012, in the other months there is growth.
  • So month by month I can show you year over year comparisons.
  • As I said, this data set has sales both for the company called VanArsdale and its competitors, other manufacturers.
  • I can see on the left side there is a chart by country that shows in orange, the sales for this company and in blue the sales for the competitors.
  • For this segment, there’s almost no sales for this company.
  • For this one it’s actually, most of the sales are, they kind of dominate this segment of the market and so on.
  • Here on the right side, I can actually see another year over year comparison, but this time of the year over year change in the market share.
  • 2011 there is no data because there is the first date in this data set, so it doesn’t have any year over year comparisons.
  • In 2014 there actually was and also in 2015 there is actually a drop in the market share compared to previous years, and again it can be calculated for a specific or for a specific segment or for all segments.
  • I have information here about sales for, as you remember, we have zip codes in countries and every year they’re getting into new territories, selling into more zip codes, which correspond to cities and states and so on.
  • If I just click on USA, you’ll see that, for the first year of course have all the locations are new because this is just the beginning of this data set.
  • For 2012 there were over 4,000 new zip codes that had some sales in this year but did not have any sales in the previous year.
  • When you look at the total locations you will see that the total growth is actually negligible, because as they were selling into new ones they were also, they had some areas in which they had sales in the previous year and not in this year.
  • So if there is like, let’s say in the U.S. 705 new zip codes that had registered some sales in March.
  • It means that those zip codes did not have any sales in any one of the months in 2011 or in 2012 up to March.
  • You can see customers that actually have bought something in the past, but did not show up any new sales in this period.
  • I have here a slicer that enables me to select for the chart on the left side, how do I want to see? Do I want to see as total percentage, or do I want to see absolutely the sales for all the competitors together and this company together? So, two ways to see the same thing, selecting it from a slicer, which is another technique I’m using here to allow the user to select things that will affect the way the visualization looks.
  • So we wanna have time to look at all the expressions here, some of them are pretty complex, but let’s at least see a few of them.
  • This one is the one that is calculated, it’s called our sales, and it’s using the calculate function.
  • Based on, there’s another one here next to it, it’s called Sales All Vendors, which is using a function we already met before, which is called all.
  • As you can expect, similar to what we saw before, there is a divide function that divides our sales by all sales.
  • The sales of our company divided by the sales of all the manufacturers together give a percentage.
  • This is the function that calculates sales in the year before the period that’s part of the context.
  • If the context selects the year 2013, this function will return the sales in 2012.
  • So this is always going to return the sales the year before.
  • Let’s say if I’m filtering on a year, I gave it all the days in this year.
  • In return, this function shifts the dates one year backwards.
  • The table is the same days that it got as input but only shifted one year back.
  • The modified context is the period, the same days last year.
  • Now once I have this, and I can do the same divide the function thing like I used before with the divide, saying, total revenue minus last year revenue divided by last year revenue, actually showed me the growth, negative or positive, between last year and this year.

Return to Summaries List.

(image source)
Print Friendly, PDF & Email