6 easy steps to make a Professional KPI in Power BI
Power BI is a platform that allows you to connect to various data sources, transform and model your data, and create interactive dashboards and reports. Power BI can help you gain insights from your data and communicate them effectively.
What is a KPI?
A KPI, or a key performance indicator, is a measurable target that shows how well an individual or a business is performing in terms of meeting their goals. KPIs can be financial, such as net profit or revenue, or nonfinancial, such as customer satisfaction or retention. KPIs help organizations track their progress, identify their strengths and weaknesses, and make informed decisions to improve their outcomes.
Building the KPI
We will be making this KPI:
Here we have YTD and YoY percentage calculations followed by a column chart of current year profit. Both these calculations require the need for a calendar table because there is a requirement of creating a calendar table whenever we are dealing with dates in Power BI.
1.DAX for calendar table creation:
Calender = CALENDAR(MIN(ecommerce_data[order_date]), MAX(ecommerce_data[order_date]))
where the arguments are the start and end dates in the main table which here is ecommerce_data and the MIN() and MAX() functions give us the min and max dates in ecommerce_data table which are nothing but our start and end dates.
Then this calendar table needs to be connected to the main table in data modelling section of Power BI. Just drag Date column in calendar table to the respective date column in main table. Refer this article for connecting the tables, it is very easy to do.
Also we require month and year for our column chart which requires DAX.
Monthnumber = MONTH(Calender[Date])
Year = YEAR(Calender[Date])
2.DAX for YoY Profit:
Now YoY Profit requires YTD Profit and PYTD Profit so we will calculate them first.
YTD Profit = TOTALYTD(SUM(ecommerce_data[profit_per_order]), Calender[Date])
Now just take a card visual from Visualization pane and place YTD Profit to it. Select the card and in Visualizations head to Format visual then to Visual expand Callout value set size 22 and Bold the font. Finally turn off Category label.
Finally you should have the following card.
PYTD Profit = CALCULATE(SUM(ecommerce_data[profit_per_order]), SAMEPERIODLASTYEAR(Calender[Date]))
Now I have already written an article about what YTD and PYTD are and how their DAX work so please refer to this link for knowing that, trust me it is very easy.
YoY Profit = ([YTD Profit] — [PYTD Profit])/[PYTD Profit]
Now just take a card visual from Visualization pane and place YoY Profit to it. Also select YoY Profit measure from Data pane then a measure tools option will come above from which click on % icon and give 2 value to decimal digits.
3.DAX for Trend Indicator Creation:
Profit Indicator = var positive_icon = UNICHAR(9650)
var negative_icon = UNICHAR(9660)
var result = IF([YoY Profit]>0, positive_icon, negative_icon)
return result
here we are creating two variables positive icon and negative icon with positive icon getting a value of UNICHAR(9650) where 9650 is unichar value of ▲ and for negative icon we are giving UNICHAR(9660) where 9660 is unichar value of ▼.
now we will create a third variable result where we run a IF condition i.e. if YoY Profit> 0(positive) then result will take ▲ and if YoY Profit<0(negative) then result will take ▼ and finally we return the result.
Now place the Profit Indicator to card visual from Visualization pane.
4.Conditional Formatting the YoY Profit and Profit Indicator:
Now we want the YoY Profit and the Profit Indicator to Green or Red colour depending on the YoY Profit value for which we will use a DAX:
Profit Colour = IF([YoY Profit]>0,”Green”,”Red”)
Its simple, IF YoY Profit>0 then Profit Colour = Green and if IF YoY Profit<0 then Profit Colour = Red.
Select any one of the cards(here we selecting YoY Profit first) and in Visualizations head to Format visual then to Visual expand Callout value set size 15 and in Color press fx.
In Format style choose Rules and in field select Profit Colour. Now in Rules type Green in the same way as you wrote in the IF function of the Profit Colour following the if values is portion and choose green colour(Hex Code #168023) then add another rule by pressing +New rule and type Red following the if values is portion and again in the same way as in IF function then just choose red colour(Hex Code #771A0C).
Now repeat the same steps for Profit Indicator card.
Finally you should have the above trend indicator.
5.Creating TOTAL PROFIT text box and Column Chart:
Go to Insert and add a Text box and write TOTAL PROFIT in it, Bold the Font, set size to 10 and choose White, 50% darker as the colour.
To create the Column chart head to Visualizations and in Build visual select Stacked column chart. Now select the chart and in X-axis put Monthnumber and in Y- axis put Sum of profit_per_order which is our profit metric.
Now the initial generated chart is for all the years but we need the chart only for the current or latest year for which first select the chart and then put Year from calendar table in Filters pane, then choose Basic filtering from the Filter type and select the latest year from the list, in my case it is 2022.
Now head to Format visual then Visual then expand Columns and in Color choose White, 20% darker.
Now expand X-axis and turn off Title and in Values choose White, 20% darker as the Color, next repeat the same for Y-axis.
Finally head to General and turn of Title and we have our chart created.
The final chart should look like this.
6.Final arrangement and KPI creation:
Go to Insert and in Shapes add a Rounded Rectangle, then select it expand Shape in Format pane and set Rounded Corners to 10%.
Now expand Style and in Fill choose White, 10% darker and turn off Border.
Now for every single element generated yet i.e. the YoY card, the trend indicator card, the YTD card, the text box and the column chart we have to turn of the background for which select them one by one and while they are selected go to Visualizations then Format visual and then in General expand Effects and turn off Background.
For text box the pane is Format and not Visualizations and there find Effects expand it and turn off Background.
Now finally adjust the size of your rounded rectangle and the elements and place the elements on the rectangle. While an element is selected you can go to Format at the top and use Bring forward and Send backward to get the right placement and adjustment.
Thus our KPI is finally completed and it should look like this.
To find max point on a line graph refer to this link.
To go to Part 1 of the Professional KPI series 1 refer to this link.
To create a YTD Sales KPI till today refer to this link.
To create a current week sales KPI refer to this link.
To create a Concatenated KPI refer to this link.
To read more stories like this you can follow me with this link.
Don’t forget to subscribe to
and join our Power BI community
