A common way to show time period changes in your KPI dashboards is to show metric indicators in the form of up and down triangles colored by the direction (+/-) of the change so that you can visually display the change in your metric over time. In Tableau you are able to copy and paste the unicode texts right into the calculations to include them in your output. Doing this same thing is a little different in Power BI, but luckily I’m here to show you a way to do this!
*Disclaimer: this is a different way of doing this than you will find on Power BI forums.Â
Step #1: Create Comparison Metrics
In this use case, I am going to walk you through how to create a calculation in Power BI that takes your metric for the most recent time period (in this case current year aka CY) and then compares it to the metric for the time period priod (previous year aka PY). I am not going to be using the SAMEPERIODLASTYEAR approach because my time periods are not displayed in actual dates. If you want to use that syntax, it will still work with my arrows building approach that I am about to show you.Â
First, let’s get acquainted with the structure of the data we will be using so we can understand how the calculations should function. This data set includes financial records for 10 years from every FBS NCAA athletics department. Each row represents one year at one school. The columns represent the different categories of expenses or revenues. There are also total columns for total revenues and total expenses. See the screen shot below.Â
For our comparison metrics we are going to create one Measure that returns the total Profits for the current year (CY) as well as the previous year (PY). Instead of hard coding the years into the formulas we are going to make them dynamic so that if you ever updated your data, or applied filters (as we’re going to do in this example), your metrics would adjust appropriately.Â
Profits CY = CALCULATE([Total Profits],FILTER(Data,Data[Year]=MAX(Data[Year])))
Profits PY = CALCULATE([Total Profits],FILTER(Data,Data[Year]=MAX(Data[Year])-1))
Each of these calculations is taking the Total Profits calculation that I’ve already done in another Measure (Total Revenues – Total Expenses + Excess Transfers Back). It is then filtering the Data table for the Year field that is equal to the Max(Year) for CY and Max(Year)-1 for PY. Once you have the PY and CY metrics, then you can create a YoY change measure.Â
Profits YoY% = ([Profits CY]-[Profits PY])/[Profits PY]
Step #2: Create Arrows Shapes
I have seen a couple of different techniques in Power BI on creating these up and down arrows. I’ve seen blogs on using the down red arrow unicode (128315) combined with using the regular up arrow unicode (9650) and coloring that one green. When I’ve done it, the arrows seemed to be different sizes so I never really like this approach. The second approach I’ve seen is using an SVG image type and coding in the shape and color based upon the YoY logic. If you are comfortable with coding this might be an approach that would work for you. Since I am not fluent in this concept I needed a simpler solution. This concept actually comes straight from how to accomplish this same task in Tableau.
We are going to create two different calculations, create two different chart elements, and then stack them exactly on top of each other so that when one NULLs out the other one populates and the arrow that has a value is showing. Here are the calculations I used to create the up and down arrow measures.
Profits YoY Change Arrows UP = IF ([Profits YoY%] > 0, UNICHAR ( 9650),"")
Profits YoY Change Arrows DOWN = IF ([Profits YoY%] > 0, "", UNICHAR ( 9660 ) )
Step #3: Implement In Report
Now that we’ve created our measures, let’s put them on the report! First, make sure to bring in your YoY% measure where you want it on the dashboard. Then create two Card report elements on your report and drag the up measure to one and the down measure to the other. One of them will be blank because the criteria is not met, but that’s what we want. Make sure to turn off the background on both Cards as they will be stacked on one another.
Click on the down arrow Card and then click into the Format option in the Visualization pane. Click into the Data Label drop down and select the color to choose your negative YoY% change. In this case I’ve chosen red. Do the same thing for your up arrow Card. Finally, drag the two Cards right on top of one another using the x and y locations to make sure that they are aligned.Â
Now your up and down arrows will flip when the metric changes and you can get those nice visually appealing arrows to show users how their KPIs are changing. See the chart below for example. When you change the years in the year filter you can see the YoY values changing as well as the indicator arrows. And voila, you have a nice addition to your KPI report!
