Professional KPI series 1 Part 2-The Area Chart
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 an Area chart of current year sales.
About this part of the series
This is the part 2 of the series. In part 1, we built the indicator part of the KPI, use this link to check it out. In this part will be making the area chart.
Part 2-The Area Chart
In this part we will be making this Area Chart:
Now this area chart requires the [profit_per_order] of our main table ecommerce_data and we need to find month and year from our calendar table Calender. To find month and year we require our calendar table which we created in part 1, use the above link to check it out.
The entire task of building the area chart will be covered in 3 steps.
1.DAX for month and year creation:
Monthname = FORMAT(Calender[Date], “MM”)
The “MM” in FORMAT function extracts the months with two digits from the dates in Calender[Date], like Jan as 01, Feb as 02 and so on.
Year = YEAR(Calender[Date])
2.Creating the Area Chart:
To create the Area chart head to Visualizations and in Build visual select Area chart. Now select the chart and in X-axis put Monthname 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 in the calendar table because our KPI is for YTD calculations so to do that 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 select the chart go to Format visual in Visualizations then head to Visual, expand Lines then in Lines expand Colors and choose the green colour with Hex Code #41DF87.
Now in Format visual in Visualizations expand X-axis and turn off Title, next expand Y-axis and turn off Title.
Finally head to General and turn of Title and we have our chart created.
3.Conditional Formatting the Area Chart:
By default the Area Chart doesn’t support conditional formatted colours i.e green colour when the trend is increasing and red otherwise, but there is a workaround for that which I learned from Isabelle Bittar.
So we need to create a copy of the above created area chart and give it red colour Hex Code #D94E58. Now superimpose both charts on top of one another.
Now select the green chart and put Profit Colour measure we used in part 1 in Filters and in Show items when the value select is and type Green below it which is same string used in IF function of Profit Colour and press Apply filter.
Now select the red chart and repeat the same process but type Red instead of Green.
When you press Apply filter of red chart the chart vanishes because our Profit Colour value is Green and not Red. When Profit Colour value will be Red then green chart will vanish and since both charts are superimposed only one of them is visible at a time mimicking conditional formatting.
Finally we have our chart.
This concludes our part 2, in part 3 we will calculate YTD profit and finally put our KPI together. To revisit part 1 or to go to part 3 refer to the links immediately below.
To go to Part 3 of the Professional KPI series 1 refer to this link.
To revisit the Part 1 of 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 ranked table 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
