“I am not like others.”
-Everyone
One of my favorite charts is A Candlestick Bar Chart. It’s a good chart if you want to show the difference in % or actual difference between two years sales or any other measure. I like this chart because it is super easy to make, its good-looking and shows the message/trends easily.
You can use the superstore data or any data of your own to build this one.
To start building the chart, follow the steps.
Step #01: Drag the Segment in rows.
Step #02: Decide for which year you want to show the difference in sales for, in my example, I will use 2021 and 2020. Write the below calculated field.
1.) Sale 2021= If YEAR([Order Date])=2021 THEN [Sales] ELSE 0 END
2.) Sale 2020= If YEAR([Order Date])=2020 THEN [Sales] ELSE 0 END
3.) Sale 2021(Copy) = If YEAR([Order Date])=2021 THEN [Sales] ELSE 0 END (We are doing this because we drag this to make the Gantt Bar)
4.) Difference= Sum([sales 2021 ])-[Sum(sales 2020])
5.) % Difference = (SUM([sales 2020])-SUM([sales 2021 ]))/SUM([sales 2020]), To this calculated field, change the Default properties, number format and convert to % (Number Format- Custom = +0.00%;-0.00%)
6.) Difference Color: Difference >0
Now follow the below steps
Step #03 drag the previous year(2020) sale in column, and current year(2021) sale to make dual axis.
Step #04
Drag the Current Year(2021) sales, and Synchronize Axis, after that make everything a bar chart.
Step #05 : Convert the current year copy in mark card to Gantt bar. In Gantt bar, put difference in size, and change size to your preference. Also put “Difference color” calculation to color part.
Step #06: Just format it and you will get a good looking chart.
Tip: You can create two parameter, and can put it in the above calculation, so that user can pick which year they want to see the difference for. That will be more dynamic as well.
For any other queries contact me:)