Power BI: calculate in percentage based on different cell values

Imagine that I have this table and I want to know the percentage for “chocolate” between February and January:

power bi

To do that, what I will do first, it is to create an index, it will allow me to select one particular cell:

  • 1. Home -> transform data -> transform data
power bi
  • 2. Add column -> index column -> from 1
power bi

So I will get this result and since I am satisfied, I will click on “close & apply”:

power bi

Now I will create a “new measure” by clicking on the right button (home -> new measure):

power bi

So to calculate the percentage between February and January for “chocolate”, let’s put this formula:

LOOKUPVALUE([Chocolate],[Index],2)/LOOKUPVALUE([Chocolate],[Index],1)

power bi

Then in the “format” field (measure tools -> format), select “percentage” and below, I will make sure that “2” is displaying. If you prefer to have only 1 or 3 decimals, just change it.

power bi power bi

Now let’s create a score card and see the result, to do that, in the “visualizations”, select the “card” button and in the “fields”, put your “measure”, and this is the result:

power bi power bi

To change the name “measure”, there are 2 options:

  • 1. Just rename for this card
power bi
  • 2. Rename it for this table
power bi

For the second option, you can also rename it by changing the name in the formula:

power bi power bi

Just take note that by renaming it in the table, it will not change it for your visualization, in this case, use the first option or remove it and put it again into the “fields” field.

Interesting Management