Introduction
This week we are making a table with microcharts (sparklines). Power BI does offer built-in sparklines, but sometimes we want formatting options not available with the defaults. Our challenge this week uses both the built-in sparklines and custom SVG images in a table.
Requirements
- Create a custom column with the following logic:
- If the Country name is the same as the region name, output the region name
- Otherwise, concatenate the country and region values
- Create a table with one row per region using the custom column created above. Add Sales and Profit (you’ll need to calculate profit as [Sales Amount] – [Total Product Cost]) to the table. Format all numeric values to have thousands separators and zero decimal places. Format Profit values to change the font color when profit is negative.
- Add a sparkline for sales amount by month to the table. Add markers for the highest and lowest values to the sparkline.
- Add a sparkline for profit by month to the table. Add markers for the highest and lowest values to the sparkline.
- Create an SVG measure that shows a column chart of Sales by Country/Region (the custom column created above), and add it to the table.
- The SVG should highlight the column that corresponds to the row in context in the table (i.e., If Canada ranks 3rd in sales, the third column should be highlighted in the SVG image in the row for Canada).
- Set the image height and width on the table properties (the example is set to height: 59 and width: 100).
- Change the alignment of the column headers for the SVG image and the two sparklines to centered.
- Set the alt text for the SVG image column in the table. The alt text should specify the rank out of the total count (e.g., “Rank: 3 of 10”).
- Create a tooltip report and set the table to use it (you’ll see the text of the SVG measure if you leave the tooltip set to the default). The example tooltip contains a matrix showing sales, profit, sales rank, and number of customers. It also uses a measure to populate the title based upon the selected country/region.
Dataset
This week’s data is AdventureWorks sample data provided by Microsoft. You can download the Excel file at https://github.com/microsoft/powerbi-desktop-samples/blob/main/AdventureWorks%20Sales%20Sample/AdventureWorks%20Sales.xlsx
You need the following 4 tables from the file:
- Date
- Product
- SalesTerritory
- Sales
Share
After you finish your workout, share on Twitter using the hashtags #WOW2023 and #PowerBI, and tag @MMarie, @shan_gsd, and @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.
I liked it very much. Well done. It was very useful