Module 5: Creating and Formatting Measures
“Creating and Formatting Measures … Using Advanced DAX Functions … Creating Measures using Advanced DAX Functions”
Summaries
- Module 5 > Creating and Formatting Measures > Lecture
- Module 5 > Using Advanced DAX Functions > Lecture
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.