Introduction
In this challenge, we are looking to add a bar with custom logic to a bar chart. Sometimes when I’m working with a customer, they want a baseline, total, or reference point to be included in the column of a column chart or the row of a table.
If it’s just a matter of adding a reference line, you can do that easily with the analyze in Power BI feature. But what if you want your reference to behave like any other category?
In this exercise we are going to add a dummy row to our source data and then modify some DAX to behave differently depending on the selected category. This is a fairly simplistic approach, if you know of a cleaner way to do this, let me know!
Requirements
- Edit the Store csv to add a new “dummy” row where the description column is set to “Average Store”. The other columns shouldn’t matter. Alternatively, you can use Enter Data in Power BI and Append in Power Query.
- In Power Query, filter out all rows where the storekey is 999999 (online sales). This is to avoid the large outlier.
- Create a DAX measure called “Quantity with Baseline”. Use IF and SELECTEDVALUE(store[Description]) to determine if we are looking at a regular store or our “Average Store” baseline.
If we are looking at a regular store, just return the SUM of quantity. If we are looking at the baseline, use AVERAGEX over the store table. Use CALCULATE(SUM(sales[Quantity])) as the expression. The CALCULATE is important because you need to perform a context transition.
- Add a clustered bar chart to the canvas. Expand it to take up the whole canvas. Set the x-axis to store[Description]. Set the y axis to our [Quantity with Baseline] measure.
- Finally, format the visual. Go to Visual -> Columns and select the category for Average Store. Set the color to something distinctive so it stands out.
Dataset
For this challenge you will use the Contoso 10k generated dataset (file csv-10k.7z) provided by SQLBI. You may need to install 7-zip to open a 7z file.
There are only 2 tables needed for this exercise:
- Sales
- Store
Share
After you finish your workout, share on Bluesky or LinkedIn using the hashtags #WOW2025 and #PowerBI, and tag @MMarie, @shan_gsd, @KerryKolosko (on BlueSky) or tag the author Eugene Meidinger (@sqlgene.com)