- Ciao, friends.
Let's solve together a scenario where we need to create
a query that computes year by year the top three products
and it also adds one additional row
to the output that contains the sales
of all the products, which are not in the top three.
Before jumping into the DAX code,
let's review the requirement together.
We have, you see already the result here
and we have four rows for 2007,
one, two, three, four
and the first row is Adventure Works, 1.2 million.
Then we have A. Datum, the third product,
and these are the top three sellers in 2007.
And then we have this additional row,
others with eight millions,
which aggregates together all the sales
of all the products, which are not in the top three.
And the same repeats year by year.
We have the same in 2009 and we have the same in 2008.
So the problems we need to solve
is first, iterate year by year.
Then we will need to compute the top three products
and finally, we will have an additional constraint,
which is creating this new row in the result,
containing others and the sales of all the other products.
While we are building the query, we will also worry about
the sort order because you see that right now,
the numbers are sorted correctly,
meaning that the first value is the highest,
then we have the second place, the third place
and the fourth contains others.
Others always appears at the end
of the list of the top three.
So we will have all these problems to solve one by one.
Now that the problem is clear,
let's jump into the DAX code and let's see how to solve it.
We will need DAX Studio.
And we need to think about how to solve the problem.
As it always happens with DAX,
we need to start from the simplest problem
and then build the query step by step.
The first step will be retrieving the products,
along with their sales amount.
Then we will retrieve the top three
and finally, we will elaborate these results
in order to add the additional row.
Let's save for now the sort order for later
because that requires a bit more thinking.
So let's start from the beginning.
We need to run a query, so we start with EVALUATE.
As I said, we need the products
and for each product, we need the sales amount.
So we start with ADDCOLUMNS.
We don't want all the products,
so we retrieve just a subset of it.
We only want the product names,
product.
And we also want a column containing the sales amount.
Let's call it amount.
Okay, let's start by looking at the first result.
Enlarge the font a bit.
And we have the product name and the amount.
Now, out of this table,
we need to retrieve the top three products.
So we want to use TOPN,
ordered by amount.
Let's format the query.
And let's see the result.
Now the result contains only three products.
Now that we have this first subset,
we need to iterate year by year
and produce for each year the list of the top three.
The function that does it is GENERATE.
And we can use, we could use VALUES of date,
calendar year, let's see how it goes.
That should return year by year,
oops, year by year the top three products.
The thing is if I run it, I have just too many rows.
Actually, I have for 2005,
it looks like all the products.
And the reason is in 2005, all the products have no sales.
Because no products have sales,
they are all in the top three.
I want to get rid of this effect,
so I need to restrict the calculation
to only the years when there are sales.
That's why it's a good idea to use SUMMARIZE here.
We SUMMARIZE sales by calendar year.
That way, I will only have the years
for which I have sales.
Let's see the result.
Now for 2007, we only have three products
and then three products in 2008 and the same in 2009.
So the numbers are correct right now.
But this is just a part
of the final solution I want to produce.
The complete solution need
to add another row containing others.
And the problem of this row is that it's not any product.
So I don't have any product containing this others row.
But I can generate a new table containing only others
and the amount and I add it to the result.
So the result will contain the three rows
plus an additional row that I will add to the result
before returning it through TOPN.
That row need to contain others as the description
and for the sales amount, for the amount produced,
it need to contain the total sales
minus the sales of the top three products.
So it's a good idea to start using variables at this point.
We store the result of TOPN into a variable,
top 3 products
that only contains the TOPN.
RETURN top 3 products.
By doing that, I'm basically returning the same result
as before but it's in a variable.
So I can now work on this variable.
Create another variable
where I will store the sales amount of all the products.
So sales of all products
is just sales amount
because I'm already in the filter context generated by
SUMMARIZE, so I'm fine with that.
Then I need the sales of the top three products.
The sales of the top three.
Actually, the value, the amount is already computed here
because ADDCOLUMNS generates for each product,
the sales amount, TOPN returns only the top three, so I only
need to iterate over top 3 products
and sum the amount column.
The number is already there, I just need to RETURN it.
Now, to check that some numbers
are actually returned, I can use this.
I create a variable here, sales top, table, sorry, here.
We see that some number comes out.
And 2.6 million should be the value
of the top three products in 2007.
Then I need the sales of others
and this is the subtraction from sales of all products.
I need to remove the sales of the top three.
This basically returns another number,
which are the sales of the other products.
Now, I have the value that I need to return.
I need to create a table containing others
and the number that I'm interested into.
So I can create a row containing others,
which is a table containing two columns.
So I need to use parentheses for that, others.
And for the value, for the number,
that will be sales of others.
Let's see what's in this table; now it's a table,
so I no longer need the curly brackets.
And so I have 2007, others, eight millions,
nine millions, nine millions.
The last thing is I need to add this row
to the top three products.
So I can now compute the result,
which is the UNION of top 3 products.
The variable containing my top three products,
to which I add the row containing others.
And I can RETURN the result.
Let's reduce this a bit so I can see the entire code.
That should work, let's give it a try.
Now I have 2007.
The three products that I'm interested into
and where is the others rows? Here they are.
At the end, I have others with the value.
It's a good idea to sort the date.
ORDER BY calendar year.
So now I have 2007, the row with others appears here in 2007
and then Contoso, Adventure Works, my four products.
Actually, the result is nice but it's not properly sorted
because you see that others appears in second place
just because that is the way it was returned.
I'm only asking for the sort by calendar year.
I can give it a try to sort by amount.
But it happens that yes, for the first year, it works,
for the second year, it works and also for the third year,
it works.
No, actually, it is not working
because I want the order to be descending by amount.
I want the top product to be the first one.
And if I do that, I'm in trouble
because now others appears as the first row
and I want it as the last in the year.
So what I need to do is I need to create a new column
that I add to the result
that will contain one, two, three, four.
So I force the order.
And I want Adventure Works to be first
because that is one million.
Second, I'm fine with A. Datum SLR.
Third is fine, Contoso, others need to be fourth.
So I want the best product to be first,
the second to be second and so on.
That looks like a ranking is a good option.
So I need to rank this table,
the table containing these three rows
with an additional column, one, two, three
that I need to add to top 3 products.
I can do that,
top 3 products.
Let's call it ranked, then do a RANKX
over top 3 products
based on amount.
That should return one, two, three
in top 3 products ranked and then instead of using
top 3 products, I use top 3 products ranked.
If I run it, I get (beautiful!) an error
that says, "column amount cannot be found."
There's no amount.
Why that?
Oh, I'm using top 3 products ranked.
Yes, that should not be a RANKX.
RANKX returns a number
that should have been ADDCOLUMNS to top 3 products,
position RANKX over top 3 products.
I wasn't creating the column.
So now I have it in top 3 products ranked.
Let's run it again.
We still have an error.
Each table of UNION must have the same number of columns.
Right now, I'm unioning top 3 products ranked
that now contains an additional column, pos,
which is not present here.
Let's put zero just as a test.
Put a comma, not a dot.
Okay, so now we have the additional column.
One, two, three for the first rows.
The problem is still with others.
I use zero but if I know that one, two, three
are always the first three, I can just use four.
So I run it again.
And I need to sort not by amount but by position.
Ascending because I want one to be the first.
Okay, now I have it, I have 2007, four rows.
There is this new row others
that I added to the result containing eight million
and the sort order is not defined by the amount
or the product name but by this additional column
that I created in the model, in the result of the query.
And, I guess, problem solved.
As you have seen, when it's time to write code
that is not trivial, that requires some time to be thought,
you never start from the beginning
and then you write the entire code.
At the beginning of this query,
you have no idea that you will use GENERATE or SUMMARIZE
or that you need all those variables.
The need arises while writing the code
and while discovering what you need step by step.
You always start from the innermost expression,
which is typically the simplest of the entire query
and then you add complexity one step at a time
until you generate a query that although looks complex
is actually not that hard
if you follow how it has been built.
Enjoy DAX.
(lively music)