4 easy steps to create a KPI with coloured trend indicator 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:
So to calculate YoY Profit first we need YTD Profit and PYTD Profit which 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.
2.DAX for YoY Profit:
As mentioned earlier 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])
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 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.
4.Adding trend colour to KPI:
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.
Now put the YoY Profit and Profit Indicator in separate Card visuals from Visualizations if not done already.
Select any one of the cards and in Visualizations head to Format visual then to Visual expand Callout value 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 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.
Now repeat the same steps for Profit Indicator card.
Finally arrange the cards to get the KPI.
Here the trend is increasing so it shows an upward arrow and YoY Profit in green colour and if the trend is decreasing it will we show a downward arrow and YoY Profit in red colour rather than green, just give it a try with other values.
To create a KPI with a unit refer to this link.
To create a Rolling 12 Months KPI refer to this link.
To calculate Running Total refer to this link.
To create a KPI with an image refer to this link.
To create a KPI with TopN refer to this link.
To read more stories like this you can follow me with this link.
References:
Don’t forget to subscribe to
and join our Power BI community
