Power BI: create a progress visual

I will show how to create 2 types of progress visual with this data:

power bi
Option 1 Option 2
power bi power bi

For the option 1, I will start by creating 3 measures:

  • Measure 1
    DIVIDE(MAX('table1'[argument1]),MAX('table1'[argument2]))
    power bi
  • Measure 2
    var num=put-your-number
    var calc1=ROUND(DIVIDE(MAX('table1'[argument1]),MAX('table1'[argument2]))*num,0)
    var calc2= num - calc1
    return
    IF(calc1>=num,REPT("▮",num),REPT("▮",calc1)) & IF(calc2<=0,BLANK(),REPT("▯",calc2))
    NOTE: change “put-your-number” by yours, it corresponds how many small square icons to display
    power bi
  • Measure 3
    var calc1=1-DIVIDE(MAX('table1'[argument1]),MAX('table1'[argument2]))
    var calc2=DIVIDE(MAX('table1'[argument1]),MAX('table1'[argument2]))-1
    return
    IF(calc1<0,FORMAT(calc2,"+0.00%"),IF(calc1=0,FORMAT(calc1,"0.00%"),FORMAT(calc1,"-0.00%"))) & " put-your-sentence"
    NOTE: change “put-your-sentence” by yours
    power bi

I will create a card visualization and put the measure 1 (left picture) then in the “reference labels” option, I will add the other 2 measures in “add label” (right picture):

power bi power bi
power bi

Now, you just have to customize it. For the option 2, I will start by creating a new table by clicking on “modeling -> new table”:

power bi

With this formula:

GENERATESERIES(1,100,1)

power bi

Then I will create 6 measures:

  • Measure 4
    power bi
  • Measure 5
    var calc=IF(MAX('table1'[argument1])<MAX('table1'[argument2]),ROUND(DIVIDE(MAX('table1'[argument1]),MAX('table1'[argument2]))*100,0),100)
    return
    IF(MAX('table2'[argument])<=calc,1)
    power bi
  • Measure 6
    IF(MAX('table2'[argument])=ROUND(DIVIDE(MAX('table1'[argument2]),MAX('table1'[argument2]))*100,0),1)
    power bi
  • Measure 7
    var calc=IF(MAX('table1'[argument1])<MAX('table1'[argument2]),ROUND(DIVIDE(MAX('table1'[argument1]),MAX('table1'[argument2]))*100,0),100)
    return
    IF(MAX('table2'[argument])=calc,3)
    power bi
  • Measure 8
    IF(MAX('table2'[argument])=ROUND(DIVIDE(MAX('table1'[argument2]),MAX('table1'[argument2]))*100,0),-1)
    power bi
  • Measure 9
    var calc1=MAX('table1'[argument1])
    var calc2=MAX('table1'[argument2])-MAX('table1'[argument1])
    var calc3=MAX('table1'[argument1])-MAX('table1'[argument2])
    return
    calc1&" ("&IF(MAX('table1'[argument1])<MAX('table1'[argument2]),FORMAT(calc2,"-0.00"),IF(MAX('table1'[argument1])=MAX('table1'[argument2]),
    FORMAT(calc2,"0.00"),FORMAT(calc3,"+0.00")))&" put-your-sentence)"
    NOTE: change “put-your-sentence” by yours
    power bi

Now I will create my visual by selecting the line chart then fill the fields like this:

power bi power bi

In the “lines” option, put 0 in the “width” field (left picture) and for “measure 7” and “measure 8”, select the “white” color (right picture):

power bi power bi
power bi

Activate the “data label” option then for “measure 4”, “measure 5” and “measure 6”, turn off the “show for this series” (left picture) and for “measure 7” and “measure 8”, turn off “value” and turn on “detail” (right picture):

power bi power bi

Into the “detail”, click on “add data” (left picture) then for “measure 7”, put “number” (middle picture) and for “measure 8”, put “target” (right picture):

power bi power bi power bi

Click on the magnifying glass to open the “error bars” option then for “measure 4”, “measure 5” and “measure 6”, activate it and configure those fields like this:

power bi power bi

And in the “bar” option, like this:

measure 4 measure 5 measure 6
power bi power bi power bi

To finish, activate the “subtitle” option then click on the “fx” icon (left picture) to select the “measure 9” (right picture):

power bi power bi
power bi

Now, the only thing to do it is to personalize it.

Interesting Topics