this video is a beginner's guide to using measures versus calculated columns
in Microsoft power bi desktop measures and calculated columns are the two ways
provided in power bi that enable you to perform calculations across your data
they're there for different reasons
personally let me give you a simple rule of thumb if what you're trying to do is
perform summary calculations across your data for example total sales last month
use measures however if you're looking at a table of your data and you want an
extra column in that table and every row off that column populated by a value
then use calculated columns especially if you want those values to be used in
further calculations later on let me show you this by example am i right I
have a data set which is my sage 50 accounts data pulled into power bi
desktop using the Santa Co bi connector for sage 50 accounts I have a table in
this data set called product sales and effectively what this is is a list of
all the line items of sales from all my sales invoices within sage 50 plus a few
extra fields let's take a quick look at some of that data firstly if I survey
date and I will choose the simple date format rather than the date hierarchy
let's add product ID and what I have now is a list of products that were sold on
particular dates let me add my sales value so that is my total sales by
product by date now actually what I want to do is I want to calculate the margin
of my sales so for that I need my cost values now what I have in my table is a
unit cost value so let's add that but of course I might be selling in multiple
quantities so I need to add quantity as well
I need is the extended cost that is the unit cost times quantity now I want to
calculate this across the entire table so I use according to my rule of thumb I
use a calculator column so let's add that now I select new column and let's
call it extended cost column
and this is simply unit cost times quantity
data set I can see their column here and I can just add that to my visualization
now my margin is my sales value - my extended cost and again I want to
calculate this for each row of my table so therefore I will need a calculated
column according to my rule of thumb
column margin column that is simply sales - my extended cost
my table and I can just add it to my visualization and a quick look at those
numbers and they they look okay I have sales of 2.6 for - a an extended cost of
1.9 - gives me a right inverse 0.72 so far so good but actually what I also
want is a margin as a percentage now margin as a percentage would be my total
margin divided by my total sales expressed as a percentage so let's try
doing that using calculated columns again and let's see what happens I'll
call this margin percentage column now I will
the safe divide function that's available in power bi which enables me
to handle the difficult situations if I have a zero denominator that function is
called divided into the numerator my margin percentage calculation will be my
sum of my margin divided by my sum of my sales
so let's do some of margin
a numerator and my denominator will be my sum of sales
and I need to tell the formula what to do in the event of a 0 denominator in
this case I want to leave it blank
there's my calculation done but I don't want to express as say as a decimal
fraction I want it expressed as a percentage so I just select the
formatting option there as percentage
and now I can look at my imagine percent in my table
if that looks a little odd to me I sort this in descending order what I can see
I've got some kind of crazy percentage numbers say which don't really make
sense I can see I've got if we take this first value first row I've got a sales
value of 33,000 647 and a cost of 24,000 390 but a margin of 428 0.19% so what's
going on there well perhaps to give a few clues let's just try removing the
product ID from the table and see what happens
and here we can see straightaway we've got an even bigger
problem if we look at the sales value on the 30th of September 2015 I've got 50
3127 I've got a cost of forty thousand eight
hundred and twenty and it's telling me I have a margin of four thousand six
hundred and forty two point five three percent now to understand what's going
on here you have to understand the way the
calculator column has worked because it has actually done what I've asked it to
do but it's calculated my margin as a percentage for each row of this table
but what I've done is I've summarized this table by date so I'm actually just
looking at the total values by date so it's adding up my sales value correctly
my total values for the 30th of September 2015 were 53,000 but what it's
done is it's added up my percentage margin for each row of the table and
added it all up to over over 4,000 percent now that's not the answer I want
now let's take a look at what would happen if we did the same calculation
using a measure which is designed for use with summaries
formula and create a new Malaysia call this margin percentage measure
format it as a percentage
and I will add it to my visualization
pop that out for maximum of you and we can see straight away we've got a
different answer in the table we're looking at summary we're looking at a
summary of sales by date now the measure is automatically taking
this into account because the way a measure works is it performs the
calculation based on the context of the visualization a measure is not
calculated before the visualization it's calculated at the time of rendering the
visualization and it automatically takes into account all the filters and the
context so so the measure takes the sum of sales by date the sum of margin by
date and calculates the margin based on that as a percentage and gives me the
right answer you might ask well why don't I always use measures to do my
calculations and if you're working with a relatively small data set and not
performing particularly complicated calculations then that's perfectly valid
but one of the things you should take into account is the fact that the
measures are calculated on the fly at the time of the visualization so if you
have a very large data set and you are performing quite complex calculations
perhaps calculations upon calculations in fact that's what we've done here
because we've taken unit cost with calculated extended cost then we've
calculated margin and then we've calculated margin percentage on top of
that these were all calculations that have to be done on the fly at the time
of the visualization so one of the things you would see if your data set is
too large or your calculations are too complex is as you try and render the
visualization you'd get a lot of wheels as spinning as the visualization tries
to render itself to avoid that if you use calculated columns
to perform the base level calculations in other words to create the basic
building block fields for the rest of your calculations and summaries and then
use measures to perform the summaries on top of that then you'll get consistently
good results and you'll also get get better performance than exclusively
using measures so back to our rule of thumb if you're performing data
summaries maybe you just want one value out of an entire table which gives you
your total sales last month's use measures if you need to create
additional base level data that is then used in additional calculations later on
use calculated columns