Workout Wednesday 2024 Week 4 | Overlapping Columns
It was my New Year's resolution to be more active in the community and join WOW challenges. Thanks to Meagan for providing an excellent opportunity for my first submission...
Challenge Overview
There is always a case where a Power BI developer would need to do a complex workaround to satisfy business requirements that would be simple enough from the stakeholders' perspective. Having a requirement like overlapping columns on a chart is not that different. Thus, I wanted to try native error bar suggestion and also have an alternative Python visual to compare it with.
Power BI
The mostly-native workaround is to use "Add further analyses to your visual" and configure the error bar. The initial setup comes with the bar bounding to the highest temperature so you can overcome this by having a custom zero measure as used in the official sample report. No major disadvantages are observed in terms of formatting besides that the inner column width does not go above what is displayed in this report.
Python
I wanted to discover my alternative to create custom visuals using Python (and maybe JS in the future) for such scenarios where I can't do something easily on Power BI maybe I can get the practice of generating practical and visually appealing custom charts. Although, this mostly would not be an ideal solution in a real-world business project for the reasons I will list below, but with such a small dataset and sake of fun I wanted to try to create a matplotlib chart.
# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script:
# dataset = pandas.DataFrame(Highest Temperatures, Lowest Temperatures, Date, Custom Format, Day Name Abbreviated)
# dataset = dataset.drop_duplicates()
# Paste or type your script code here:
import matplotlib.pyplot as plt
import matplotlib as mpl #necessary to customize style
#Set visual formatting
plt.style.use("fivethirtyeight") #Use pre-defined style from docs
mpl.rcParams['font.size'] = 9
ax = plt.axes()
ax.set_color = 'whitesmoke' #Use pre-defined color from docs
#Bring Power BI Data
Hi = dataset['Highest Temperatures']
Low = dataset['Lowest Temperatures']
ZC = dataset['Custom Zero Col']
PCF = dataset['Custom Format Python']
#Set Upper and Lower boundries
as_error = [Low, ZC]
#Create the chart and the error bar
plt.bar(PCF, Hi, color="#DE6A73")
plt.errorbar(PCF, Low, yerr=as_error, fmt="o", color="#74ADD1",
linewidth=20)
#Show the visualization
plt.show()
I've tried a similar approach taken for the Power BI and set the lower bound to a custom column set to zero. Also another challange was to use both 'Date Table'[Day Name Abbreviated] column and 'Date Table'[Custom Month Day] columns together on X-axis so created a new column called [Custom Format Python] to display Jan XX Wed format.
Disadvantages of using a Python Visual
- Up to 150,000 rows are displayed.
- Low resolution.
- No interactivity like highlights.
- A security warning pops up to enable using it.
- Hard to format around auto-generated padding and margin.
Advantages of using a Python Visuals
- More customizable formatting options (i.e inner bar chart width on this report)
- Extensive example visuals around the community for supported visualization libraries like matplotlib or seaborn.
- Great supportive tool.
Conclusion
In summary, using the further analysis option gives us a good ability to generate overlapping inner columns unless stakeholders (client, BA, designer) want more control over the bar formatting. Python can contribute such situations to provide this flexibility, however, its limitations should be considered carefully. Who know the next challenge we can create a JS visual and compare all three together.
Additional Tools Used/References
- DAX Studio - Measure creation and DAX formatting
- FiveThirtyEight matplotlib style sheet
- Colors inspired by The New York Times Graphics