- Ciao, friends.
Today, I want to show you how to compute
a total by summing values row by row.
And I want to do that by starting with a wrong measure.
Let's take a look at this report.
In this report, I'm showing the sales amount
by several months in 2007
and then I'm computing the sales per working day,
which is the amount of sales
divided by the number of working days
month by month.
The number is wrong and how do we know it?
Well, sales per working day
should look like some sort of average
but all the values in every month
is greater than 4,000
and it's kind of weird that the last value,
the average over the entire year,
is less than 4,000.
Where is the problem?
Well, the problem is that sales per working day
is defined as the sales amount
divided by the number of working days.
So, the measure by itself is just a simple division.
Working days might be the problem.
In order to debug it, we just take working days
and we put it in the report to see the different values.
If we do that, we put working days in the report
and you see that we have sales amount,
we have working days,
and the problem becomes clear.
We are dividing the sales amount,
which is 885 divided by 261 days,
many more than the working days month by month.
I'm taking into account values
which should not be considered.
And the reason I don't want to consider them
is because I don't have sales.
Now, you can hide this value in an easy way
because if I want to remove working days
from the report, an IF statement is more than enough.
I can just use an IF statement
that checks if sales amount is greater than 0
then I compute the number of working days.
Otherwise, I blank it out.
And as a result, you see that now
the number is no longer visible
but still the total is wrong.
Why that?
Well, even if we are not showing the value row by row
we are still taking that into account at the grand total.
So what I want to do is
instead of doing the calculation only at the total level,
I want to move the calculation row by row,
perform the calculation there
and then aggregate the result.
What am I doing if I do an operation
step by step, month by month?
I am iterating.
And I am iterating over what?
Over the month.
So, I need to replace my calculation
and instead of doing a simple formula like this
I want to iterate it month by month.
So what I need to do is replace these working days
with a different calculation
that is a SUMX over the VALUES of what?
Well, in my report I have the month
but in order to make sure that it will work
also across multiple year,
I would like to use a column
that contains both the year and the month
in the same column
so that I will always iterate on different values.
So, I iterate over the VALUES of Calendar Year Month
and then I use CALCULATE to perform context transition
and force the entire calculation
to only the current month.
I need to format the code the right way.
These need to be there.
Now, it looks much nicer
and by doing that, I do an iteration month by month
and I compute the sales amount
and the number of working days
only if there are sales month by month.
If you take a look at the total, it was 261.
Now, it is 152, which happens to be exactly
the sum of the values row by row.
And of course my original measure,
which divides sales amount by number of working days,
is now computing the right number.
Have fun with DAX!