Here’s Zoe’s entry for Power BI Challenge 9. @datazoe, would you like to share how you built this dashboard and what your inspiration is in building it?
Here is the link to the report:
To learn about the real-life scenario presented for the challenge, be sure to click on the image below.
For this report I wanted to focus on profitability of recycling. I recently read an article about how labeling plastics where terrible for recycling because plastic is so expensive to recycle. Before the labels they would recycle milk jugs at a loss because the other materials made up for it, but once labels were introduced people saw other plastics were the same label as those milk jugs and also included them. So they had much more plastic and no longer did the other materials compensate for the losses of recycling it. I felt a little bad, because I know I felt good about recycling my plastics not realizing how troublesome it was. I’ll find the article and link it later, but here is another one too: https://www.news.ucsb.edu/2020/020072/closing-plastic-loop So now I could look at it with data.
Profitability, and specifically unprofitability, was what I wanted to highlight. I colored unprofitability in red and then for profitable I chose to fade it out toward the background color. The icons I did keep blue, but otherwise, I stuck to the black/grey/white colors to give highest visibility to the unprofitable. Trying to utilize the design principle of contrast.
I also kept that unprofitable coloring even when I switched topics on the other pages. That way the user would know, ok we had great sales in this grade and yet it was still unprofitable, and they could look at the details in that context.
I did get more colorful by the 3rd and 4th tab as I wanted to try and show Buyer/Supplier by grouped grades, by flipping the charts but I am not sure I liked it. The tooltips also have more color, with a specific color for profit, sales, hauling costs, and purchase costs tooltips. I wanted to a way to visually represent that the topic had changed.
For the currency conversion I utilized https://www.daxpatterns.com/currency-conversion/#. I am sure the content here is fantastic but I do not like watching long videos so I prefer the instructions written out in an article. Finding currencies was annoying but I did find it by month for each of the three currencies then provided the conversion between them. I modified the measures to handle the same source/target currency and took out the error handling (because I preferred to see where it worked and where it didn’t to troubleshoot).
Sales (Converted) =
IF (
HASONEVALUE ( 'Target Currency'[Currency] ),
VAR AggregatedSalesInCurrency =
ADDCOLUMNS (
SUMMARIZE (
Trading,
'Date'[Month Year],
-- Month granularity
'Source Currency'[Currency]
),
"@SalesAmount", [Sales],
"@Rate",
SWITCH (
TRUE (),
SELECTEDVALUE ( 'Target Currency'[Currency] ) = 'Source Currency'[Currency], 1,
CALCULATE ( SELECTEDVALUE ( 'Monthly Exchange Rate'[Rate] ) )
)
)
VAR Result =
SUMX (
AggregatedSalesInCurrency,
IF ( NOT ( ISBLANK ( [@Rate] ) ), [@SalesAmount] * [@Rate], BLANK () )
)
RETURN
Result + 0
)
I kept this as it was so I could use it in charts, then added another one that formatted it:
Sales (Converted/Formatted) =
VAR _leading =
SELECTEDVALUE ( 'Target Currency'[Leading] )
VAR _format =
CONCATENATE ( _leading, "#,##0.00" )
VAR _return =
IF (
ISBLANK ( [Sales (Converted)] ),
BLANK (),
FORMAT ( [Sales (Converted)], _format )
)
RETURN
_return
The annoying part was I had to do this with every measure. I probably could have looked into calculation groups.
As the formatting was more text than number, I still had to use the converted number only on charts. This meant the tooltips also included the non-formatted version by default. To get around this I included a tooltip to show formatted values for all the values used in the charts. This allowed for some more insight to how the number was derived, for profit, and the range of numbers found, in the case of a cost or sale per grade or supplier/buyer. I had originally tried to use a scatterplot but it would not work nicely small, so I fell back to my favorite box whisker. I also used the enlighten data story. While the new narration visual could also have been used, I find it super annoying to modify.
I also wanted to do some looking a the change in currency, as while we did not get geo data, it can be inferred from the currency conversions. For this I utilized the Sankey chart, which gives a great view of the currency flow.
For analysis, I chose the measure per unit in a way to try and normalize the data and allow comparison of haulers, suppliers, and buyers. And even to compare these measures over time in a way that accounts for the huge uptick in jobs in November and December.
Finally, I used the free numerro template for the design base. This way I could share the PBIX as it’s not using premium paid content. I did modify the technique a little on the sidebar (to use buttons with image fill instead of images), but not the look and functionality.
I didn’t get to sorting out all the fonts and syncing of the slicers, and also I thought I should probably make the tooltips accessible through drill through too.
Overall this took me a couple days to do. I had the idea of what I wanted to do, but the biggest chunks of time went to getting the currency conversion to work, then followed by messing around with all the tooltips and functionality of the report.
Once again love the design Zoe.
I can see you’ve utilized a similar template, but honestly it looks superb. I love how you’ve been able to utilize different different information and data in a very similar navigation and design perspective.
I think in some of the visualizations especially the supplier one you’ve got a little bit too many colors there. So have a good think about how you could maybe represent that in a better way. And also the buyer one. Maybe using a different visualization type would have been better there.
Overall I love this template I think it looks superb and is a great inspirational reports that other users could utilize to manage and dream up their own designs.
Impressive write up. Tooltip is phenominal!
Sam
Thanks @sam.mckay. What I like about these challenges is that I can play around with different things to see if they work out or not.
