Ciao friends.
In this video, I want to show you how to create a KPI
using a Power BI Desktop and Tabular Editor together.
Let's start by analyzing the business scenario.
At Contoso, they want to do an analysis
of the margin percentage by category and by year.
And compare the percentage of each category and year
with the overall margin percentage.
If it happens that for a combination of category and year,
the percentage is below the overall, that is bad,
if it is above, that is good,
if it is around the same value as the overall percentage,
that is average.
They want to show all these information,
using a graphical presentation, a KPI.
Let's start by looking at the final result I want to obtain.
They want to build a report like this.
We have the overall margin percentage as a reference.
Then the report is showing by category, and by year,
the margin percentage, plus the status.
The status is red the for audio in 2007, because 48.90
is way below 53.
Whereas cameras and camcorders is green
because it's around 60%.
So it's very good.
Not only they want to show the status,
they also want to compute the trend.
Comparing the percentage of the current year
against the previous one.
That is represented as an arrow that goes up or down,
whether the margin percentage is increasing or decreasing.
Now you could build everything using conditional formatting.
But if you did that, you will end up,
repeating the conditional formatting process
for all of your reports.
Instead, what I want to do is use Tabular Editor
to create a KPI in my report
so that we will be able to use them both
in Power BI Desktop and in Excel.
But before we move further,
we need to understand just a bit of the theory.
What is a KPI from the point of view of Tabular?
A KPI is basically a measure on steroids.
You can turn any measure into a KPI
and once a measure becomes a KPI,
it gains additional properties.
You will be able to define the value of the KPI,
which is the same value as the measure.
But then you can define the target, the goal in our case,
the overall margin percentage,
and the two graphical indicators, the status and the trend.
All these are DAX expressions that can be defined
in any measure, which is turned into a KPI.
We are now going to do that step by step.
So instead of using this report,
that already contains everything
we start from a different one that contains
only the margin percentage.
If we want to create a KPI,
we need to first start from external tools,
launch Tabular Editor.
Tabular Editor provides us a user interface to create a KPI.
As I said, any measure can be turned into a KPI.
What we want to do is turn the margin percentage into a KPI.
To do that you go to create new KPI.
Now I have this additional KPI feature,
and from here, I can choose the status,
the target and the trend expression.
Let's start from the target.
Target is the number we want to use as a comparison.
I'm not going to type in all the DAX code,
I just copy from the working solution.
And the target expression is the margin percentage overall.
This 53%.
I confirm it.
Before I save it, look what happens here
at the margin percentage.
Right now, it is just a measure.
But as soon as I click on file save,
it is no longer a measure, it became a KPI.
And if you expand it, you see that we have a value,
a goal and a status.
The value is already in my report.
If I put the goal, the measure that I define right now,
you'll see, it shows 53% everywhere.
Now I'm not interested in looking at the goal,
I want to use the goal in order to compute the status.
The status is another DAX expression.
So we go back here and we define the status expression.
Let me copy it where it's already working.
So copy and paste.
How do we computer the status?
The status will be represented
as a graphical representation and it has three values.
Minus one for bad, zero for average, one for good.
So we first compute the percentage,
then we use a tolerance of two percent, so
not to be too strict on the comparisons,
and we compute the goal in three variables.
Then we use them depending whether the percentage
is below the goal, minus the tolerance,
then we record minus one.
If the percentage is above the goal, plus the tolerance,
we return one.
Otherwise we return zero, three different numbers.
And this defines, the value of the status.
We still need to define what to use
as a graphical representation.
That is defined here in the status graphic.
We want to use a traffic light.
Once everything is set, we now file, save,
go back to Power BI Desktop,
and if I add the status to my report,
you see, I have margin percentage status,
which is being shown as a traffic light.
Time to define also the trend.
For the trend, we want to compare the current value
with the value in the previous year.
So we go to the trend,
let me again copy the working version,
(clicking)
and expound this a bit.
(clicking)
Let me go to target them back to trend, (clicking).
Okay.
So what do I compute?
I first compute the margin percentage
of the current value,
then the previous margin percentage,
the margin percentage in the previous year.
And once that is done,
I compare the margin percentage in the current year,
against the margin percentage of the previous year.
And if the percentage in the current year
is greater than the previous one,
I return one, otherwise minus one, otherwise, zero.
Again, this is just computing a number.
I need to choose the graphical representation of this value,
and I do that by choosing the trend graphic
for which I use
a standard arrow.
Here it is.
I have defined the trend, Control + S to save.
And if I go back here, you see the trend
is not visible in the list under the margin.
That is normal Power BI did not refresh correctly,
but it is enough to collapse the fields,
expand the fields again so that it refreshes all the values.
Now under margin percentage I have my trend.
I can put it in my report and have the margin,
the percentage and the trend,
which is blank for the first year and start showing values
starting from the second year.
Unfortunately, Power BI uses measure names
to expose the status and the trend,
and these names, margin percentage, status
and margin percentage trend, are chosen by the engine
and you cannot modify them.
Therefore, in order to avoid wasting all this space,
you need to rename them in the report.
So we go here and we rename margin percentage status
to just status, and margin percentage trend,
where is it, rename to just...
trend.
And here we go.
Now we have created a report that is using KPIs,
which are stored in the model.
As you have seen, KPIs are easy to use
they are easy to implement,
and they give you a tremendous power,
because you have the option of turning any measure
into a KPI, adding the goal, the status and the trend.
You would need to pay attention
because of the behavior in Excel and in Power BI
is slightly different.
But in order to analyze that, that will be much deeper,
and so we leave the topic for another video.
For now play with KPIs start building yours and enjoy DAX.
(Upbeat music)