- Ciao, friends.
In a previous video, we have seen how to create KPIs
with Power BI Desktop.
In this video, we want to understand the differences
between Excel and Power BI
when you consume a KPI published on a Power BI report
from different clients
'cause you can use Analyze in Excel,
connected to a dataset published from Power BI.
So let's take a look at an example
where I have a report here
with status and trend, which are two KPIs assigned
to the revenues measure.
Remember, you create these KPIs
using Tabular Editor.
In Tabular Editor,
you can edit the KPI properties,
changing the graphic
and the expression for trend and status.
Remember, this is described in a previous article
and video about this topic.
So what I want to focus this time
is what changes when I use Analyze in Excel,
I create a pivot table
and I consume the same data that you see here.
In this particular case,
I obtain a very similar report.
You see that the status and the trend
have the same graphical representation.
However, if I go back to Power BI,
and I change this visualization
to this other page where I use a different graphic
for status and trend,
you should notice that status now has five different states
and you see that there is a pie chart in this case.
If I go to Excel
and I try to show the same KPI here,
not only I don't get the right visualization
in all the cells,
'cause you see that I have empty cells here
but I also have a different visualization
for the status,
and also the arrow, the trend is different.
In order to compare the two graphics,
I prepare here a side-by-side comparison
where you see that in the first case,
we had exactly the same visualization
but now, I'm using a status
that has five states, a pie chart
and the pie chart itself,
you see that in this case,
we have a quarter here
but in this case, we have an empty state.
For this state that is empty,
we have blank here
and the same happens also for the trend.
You see that the trend that is negative here
doesn't have any representation here.
So why this is happening?
Well, the idea is that there is a different representation
between Power BI and Excel
of the range of values
when you have the graphic that has five states.
We can see this in an example
that I prepared here where you see that in Power BI,
we have different graphics that have five states
but the state has a number
that is an integer value between minus two and plus two
whereas those visualizations with three states
have minus one, zero and one.
Same as in Excel for the three states,
even though you see that the graphic could be different.
I will recap this in a moment.
But when you look at the five-states visualization,
here Excel has a different representation
between minus one and one
and the decimal values are recognized as valid values.
So what happens if we try to use the range
of values we have in Excel
to optimize our KPI?
So I can show you here.
This example has been optimized for Excel.
You see that Excel now has the right visualization here.
But at this point, you see that in Power BI,
when the number is not an integer value,
you see a decimal value
and not a graphical representation.
So because of these differences,
it's important to plan if you want to use one client
or both clients and choose those graphics
and those range of values that are compatible in both
or that are optimized for a specific client,
like we did in this case.
In this example, I created a specific KPI
for Excel and another one just for Power BI.
Of course, you create multiple KPIs this way
but if you want to have the maximum range
of graphics in both environments,
this is what you have to do
because the visualization with different ranges
doesn't provide you any other choice.
So in the article, you will see a lot of details
about the graphics that we suggest to use.
And also, the example of the calculation
that you can implement, you can download the examples
with all the possible combinations of KPIs
that you can obtain.
Enjoy DAX.
(lively music)