Introduction
Welcome back to Workout Wednesday! This week’s challenge is for folks who want to practice building reports that do have calculations but do not use complex DAX!
I also had a little fun with Microsoft Designer for the images. If you haven’t had a chance to play around with this new AI design tool I recommend you give it a try! Dataviz isn’t always the best place for fancy images, but there’s a time and a place for everything!
Today we’re creating a couple of stacked bar charts, using an implicit measure and a visual calculation*, adding a bit of informative text, and creating custom navigation using bookmarks and buttons! If you’re new to Power BI this will be a fun one!
*Visual calculations are only available in the February 2024 build or later
Note: because the cumulative profit chart uses visual calculations, and this feature is in preview, it does not work with the embed above. Here is what that chart should look like:
Requirements
Get data
- Import data from Excel
- Feel free to poke around in Power Query, but there are no transformations required for this report
Create visuals
- If you’d like to add a custom background, find a suitable image
- The prompt I used for the background is, “background for an analytics report for a bookstore with lots of white space”
- Create a stacked bar chart displaying the number of books sold by language
- Customize your data colors to match your theme. I use the Colour Contrast Analyzer app to check my color contrast and get HEX codes using their eyedropper tool.
- Create a navigation menu using buttons. As you set up your buttons, you can also set up your bookmarks and use the selection pane to show/hide different chart elements.
- Tip: name your bookmarks in a way that makes sense! Later you will appreciate it.
- Create a second stacked bar chart to display the total profit over time, cumulatively.
- Here you’ll add Profit to the values field well, where you’ll notice that it automatically becomes the Sum of Profit. This is called an implicit measure. It’s a calculation automatically created by Power BI that is attached to the visual.
- Now you’re going to use the new Visual Calcs feature to create the cumulative profit using the Running sum template.
- All you need to do is: Cumulative Profits = RUNNINGSUM([Sum of Profit]
- Add a title and any other design elements that you’d like! I added descriptive text, assuming users needed a bit of guidance/explanation.
- Ensure that all visuals meet the minimum standards of having alt text, that the tab order in your report makes sense, and that your color choices meet the minimum contrast standards.
Dataset
You can find this week’s data as a download from Excel. You’ll then get data from Excel in Power BI desktop. This is fictional data.
Share
After you finish your workout, share on Twitter using the hashtags #WOW2023 and #PowerBI, and tag @MMarie, @shan_gsd, @KerryKolosko. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year.
Solution
Solution File available for download via the data stories gallery.
Hi Shannon,
When I opened the solution file which was downloded via the link provided, I can’t see the report by clicking the buttons.Is there anything I did wrong?
Thank you!
Hi Richard! Good callout! I was struggling with the way I created the buttons, and I posted the wrong version! You’re not doing anything wrong, I am! Here’s a link to the right .pbix file: https://4lbi-my.sharepoint.com/:u:/p/shannon/EW2IJhZM2cZMixM0qDlDduIBJgFo710z-mdaLEbv8gnFKg?e=LPhkDU
Thank you so much for pointing this out!
Hello!! I am unable to get the excel file. It is not on my excel templates. Can you suggest other means of getting the dataset? Thank you
Hi Murnsurah – are you able to download the Excel file from here?
You then need to go to File > Save As > Download a Copy
Let me know if this doesn’t answer your question!