Latest Enterprise DNA Initiatives


Power BI Challenge 9- Currency Conversion and More

Hi All:

Here’s my incomplete submission for this challenge. My main challenge (sorry for the pun) in this challenge was lack of time, as being an independent consultant with a September year-end, this is tax time for me (not to mention the demands of my day job and that November is also contract expiration/extension month, so extra negotiations/paperwork). So, I did what I could in the time I allotted myself and thought I’d submit and post my incomplete solution anyway in case others might find some of my ideas useful.

Landing page:


Summary page (unfiltered):

Summary page (filtered:

Filter page:

I really liked the simple navigation of simple chevrons I used in my last eDNA challenge submission, and was sorely tempted to use it again (as I think it looks and works great), but attempted to try something new.

So, for what its worth, here are the notes I kept during my (incomplete) development.

(My apologies for the poorly formatted DAX; I wanted to use the “Hide Details” tool to collapse my sections, and once I did so, I could not get the DAX formatter to react properly, so …)

Edit: My mistake … it wasn’t the “Hide Details” syntax that was preventing the built-in DAX formatter from working properly; it was the bullet points, so I entered 3 dashes at the left margin above and below each code block, and voila.

Attributes:


What I liked:
  • I’m glad to have taken the opportunity to explore currency conversion; although I didn’t spend much time verifying the results, the basic mechanics of currency conversion are now in my arsenal, and I can explore further when the need arises
  • I like that my solution is not dependent at all on choosing a base currency, but rather give the user the option to select whatever display currency they like by doing the conversion twice (once transaction currency to base currency, then once from base currency to display currency)
  • I’m happy with the addition of grouping to the [Grades], as instead of 67 separate grades I can now view 4 different “Materials” and hopefully extract more insights
  • I like the cleanliness of moving all slicers to a separate [Filter] page, then “echoing” the selected values in a matrix on the Summary page
  • I like the cleanliness of simple shadows on all visuals
What I didn't like/what needs more work:
  • I’m not happy with not being able to validate the calculations; it seems to me that the amounts are not being presented correctly, but unfortunately, I didn’t have the time to investigate further. (I think this is probably from just going too fast with [and/or insufficient investigation into the] the business rules…)
  • I’m disappointed with only being able to spend a limited amount of time on this challenge and did not have the opportunity to explore anything but the most basic visuals
  • I didn’t find a way to remove or minimize the blank space at the top of the slicer selection “echo” matrix on the [Summary] page; my original intent was to align this vertically at the top of the page, but couldn’t figure it out in the time I allotted myself for this challenge
Goals:
  • to gain experience doing currency conversions (which I had not done before)
  • to try to reduce the navigation even further from my eDNA challenge #8 submission, by using a single page only and moving all filters off-page and using simple “funnel” icon to access all filters
Setup:
  • used Power BI Desktop November 2020 release (Build 2.87.684.0 64-bit, date published 12-Nov-2020)
  • used the Filter Pane visibility “eye” to hide the Filter Pane from report readers
  • browsed the Power BI Tips Theme Gallery and chose a selection of blue-based palettes
  • extracted the colour HEX codes from these palettes, and put the HEX codes one-at-a-time into the eDNA Colour Fan https://analysthub.enterprisedna.co/apps/colour-generator/colours-fan#18528B until I found one a palette I liked
  • using #18528B as the base colour, copied the HEX codes from the colour fan into a text file, then used them to generate a JSON theme file with an 8-colour theme using Power BI Tips Theme Generator https://themes.powerbi.tips/ (#B1D1F2, #85B8EA, #599EE3, #2E85DC, #1F6BB7, #18528B, #103860, #091F34)
  • used PowerPoint to create a simple horizontal line background (in theme grey, white 50% darker #808080), exported as PNG, then used as page background to separate the header from the body of the report (used PowerPoint as I could get edge-to-edge line, as the built-in line in Power BI left a gap at both left and right)
Currencies:
  • reviewed eDNA YouTube video on How To Download Latest Exchange Rates Into Your Power BI Model (https://www.youtube.com/watch?v=9oPFgHvGyKc)
  • chose CAD (Canadian Dollar) as the base currency and sourced daily exchange rates as a CSV file from the Bank of Canada for all available currencies for the date range from 2019-01-01 to 2020-11-06
  • loaded the CSV file into Power Query and named as [FX]
    • used “Remove Rows” to remove top 33 rows
    • used “Used First Row as Headers” to promote first row
    • renamed [date] column to [Date], [Attribute] to [Currency Pair], [Value] to [Exchange Rate]
    • selected the [Date] column and unpivoted other columns
    • used “Column From Examples” and extracted foreign currency from [Currency Pair]; renamed this as [Foreign Currency]
    • used “Column From Examples” and extracted CAD from [Currency Pair]; renamed this as [BaseCurrency]
    • removed [Currency Pair] column
    • filtered [Foreign Currency] column to show only AUD, CHF, EUR, GBP, INR, JPY, NZD, USD
  • summarized the foreign currencies and base currency, their codes, and their symbols in Excel and loaded into a new [Currencies] table
  • created reference of [Currencies] table as a second currency table [Display Currencies] to use in “Display Currency” slicer
Data Loading/Data Transformations:
  • used eDNA extended date table M code as [Dates] Extended Date Table (Power Query M function)

  • marked [Dates] table as a date table

  • changed all date formats in [Dates] table to dd-mmm-yyyy [by typing “dd-mmm-yyyy” into “Format” box directly]

  • created empty tables for [Key Measures], [Admin Measures], and [Narrative Measures]; opened 2 sessions of Tabular Editor (1 each on my Challenge #8 [C7] PBIX and this file) and copied measures

  • added [Last Refresh] table (used Power Query in both my Challenge #8 PBIX and this file to copy-and-paste)

  • added [fxNow, fxToday, fxStartDate, fxEndDate, fxFirstDataDate, fxLastDataDate] parameters (used Power Query in both C8 PBIX and this file to copy-and-paste)

  • changed [fxFirstDataDate] to use earliest Trades[Estimated Load Date] date; changed [fxLastDataDate] to use latest [Delivery Date]

  • modified [Dates] query to use now-available [fxStartDate] and [fxEndDate] parameters

  • edited error rows in [Challenge 9 Data.xlsx], replacing “#N/A” in “Haulier” column with “Unknown 999” in 2 rows (780 and 781)

  • imported [Challenge 9 Data.xlsx] as staging query [RAW Trades]; disabled load; moved into new group [Staging Queries]

  • created reference of [RAW Transactions] as [Suppliers], chose only [Supplier] column and removed other columns, removed duplicates

    • added [Supplier ID] column using “Column From Examples” to extract ID; changed data type to “Whole Number”, then sorted table by [Supplier ID]
  • created reference of [RAW Transactions] as [Buyers], chose only [Buyer] column and removed other columns, removed duplicates

    • added [Buyer ID] column using “Column From Examples” to extract ID; changed data type to “Whole Number”, then sorted table by [Buyer ID]
  • created reference of [RAW Transactions] as [Haulers], chose only [Haulier] column, duplicated column and renamed duplicate to [Hauler], removed other columns, removed duplicates

    • added [Hauler ID] column using “Column From Examples” to extract ID; changed data type to “Whole Number”, then sorted table by [Hauler ID]
  • created reference of [RAW Transactions] as [Collection Types], chose only [Collection Type] column, removed other columns, removed duplicates, removed nulls, sorted ascending

    • added [Collection Type ID] column using “Add Column \ Index Column \ From 1”
  • created reference of [RAW Transactions] as [Account Managers], chose only [Account Manager] column, removed other columns, removed duplicates, sorted ascending

    • added [Account Manager Type ID] column using “Add Column \ Index Column \ From 1”
  • created reference of [RAW Transactions] as [Grades], chose only [Grade] column, removed other columns, removed duplicates, removed nulls, sorted ascending

    • added [Grade ID] column using “Add Column \ Index Column \ From 1”
  • created reference of [RAW Transactions] as [Process Flow Stages], chose only [Process Flow Stage] column, removed other columns, removed duplicates, removed nulls, sorted ascending

    • added [Process Flow Stage ID] column using “Add Column \ Index Column \ From 1”
  • created reference of [RAW Transactions] as [Customer Load Types], chose only [Customer Load Type] column, removed other columns, removed duplicates, removed nulls, sorted ascending

    • added [Customer Load Type ID] column using “Add Column \ Index Column \ From 1”
  • [Transactions]:

    • created reference of [RAW Transactions] as [Transactions]
      • renamed [Est Load Date] to [Estimated Load Date]
    • changed all date formats in [Transactions] table to dd-mmm-yyyy [by typing “dd-mmm-yyyy” into “Format” box directly]
    • merged [Transactions] table with [Suppliers] table, keeping only [Supplier ID] column and removing [Supplier] column
    • merged [Transactions] table with [Buyers] table, keeping only [Buyer ID] column and removing [Buyer] column
    • merged [Transactions] table with [Haulers] table, keeping only [Hauler ID] column and removing [Haulier] column
    • merged [Transactions] table with [Collection Types] table, keeping only [Collection Type ID] column and removing [Collection Type] column
    • merged [Transactions] table with [Grades] table, keeping only [Grade ID] column and removing [Grade] column
    • merged [Transactions] table with [Process Flow Stages] table, keeping only [Process Flow Stage ID] column and removing [Process Flow Stage] column
    • merged [Transactions] table with [Customer Load Types] table, keeping only [Customer Load Type ID] column and removing [Customer Load Type] column
    • duplicated [Purchase Currency] column, then renamed to [Purchase Currency Code]
    • replaced values in [Purchase Currency Code]: “(£) - Pound Sterling” with “GBP”, “(€) - Euro” with “EUR”, “($) - US Dollar” with “USD”
    • repeated above step for [Sales Currency] and [Haulage Currency] (didn’t use interface; rather, copied M Code for [Purchase Currency Code] value replacements and pasted twice, then adjusted “pastes” for [Sale Currency Code] and [Haulage Currency Code]
    • reordered columns in the Data View for [Transactions] table by using “hack” described in eDNA Forum post; bracketed the procedure by taking a screenshot of the data model, deleting all relationships. performing the “hack” (Tip: How to Change Column Order in Data View), then using the data model screenshot to re-create the relationships
  • added calculated column for [Transaction State] as follows:


Transaction State ID = 
VAR _GradeBlank = IF( ISBLANK( Transactions[Grade ID] ), 1, 0 )
VAR _PurchaseDateBlank = IF( ISBLANK( Transactions[Load Date] ), 1, 0 )
VAR _PurchaseWeightBlank = IF( AND( ISBLANK( Transactions[Total Supplier Weight] ), ISBLANK( Transactions[Est Weight] ) ), 1, 0 )
VAR _PurchasePriceBlank = IF( ISBLANK( Transactions[Unit Purchase Price] ), 1, 0 )
VAR _PurchaseCurrencyBlank = IF( ISBLANK( Transactions[Purchase Currency Code] ), 1, 0 )
VAR _HaulageCostBlank = IF( ISBLANK( Transactions[Haulage Cost] ), 1, 0 )
VAR _HaulageCurrencyBlank = IF( ISBLANK( Transactions[Haulage Currency Code] ), 1, 0 )
VAR _SaleDateBlank = IF( ISBLANK( Transactions[Delivery Date] ), 1, 0 )
VAR _SaleWeightBlank = IF( ISBLANK( Transactions[Total Buyer Weight] ), 1, 0 )
VAR _SalePriceBlank = IF( ISBLANK( Transactions[Unit Sales Price] ), 1, 0 )
VAR _SaleCurrencyBlank = IF( ISBLANK( Transactions[Sale Currency Code] ), 1, 0 )

RETURN
SWITCH( TRUE(),
    _GradeBlank > 0, -1,
    _PurchaseDateBlank + _PurchaseWeightBlank + _PurchasePriceBlank + _PurchaseCurrencyBlank > 0, -2,
    _HaulageCostBlank + _HaulageCurrencyBlank > 0, -3,
    _SaleDateBlank + _SaleWeightBlank + _SalePriceBlank + _SaleCurrencyBlank > 0, -4,
    1
)

[Grades]:

  • added [Material] calculated column to [Grades] table to permit categorization into 4 groups: paper, plastic, metal, and other

Material = 
SWITCH( TRUE(),
    LEFT( Grades[Grade], 5 ) = "Paper", "Paper",
    LEFT( Grades[Grade], 4 ) = "HDPE" || LEFT( Grades[Grade], 4 ) = "LDPE" || LEFT( Grades[Grade], 14 ) = "Mixed Plastics" || LEFT( Grades[Grade], 3 ) = "PET" || LEFT( Grades[Grade], 2 ) = "PP", "Plastic",
    LEFT( Grades[Grade], 9 ) = "Aluminium" || LEFT( Grades[Grade], 5 ) = "Metal" || LEFT( Grades[Grade], 5 ) = "Steel", "Metal",
    "Other"
)

  • used similar formula to create a second calculated column Grades[Material Sort] (instead of “Paper”, 1; “Plastic”, 2; “Metal”, 3; “Other”, 4)

[States]:

  • added calculated table for transaction states
    eDNA Challenge 9 - Currency Conversion - Greg Philps - States
  • used “Properties” pane in “Model” view to hide all ID fields
  • organized all [Admin Measures] into subfolders using Model view as displayed in @JarrettM’s video https://www.youtube.com/watch?v=J_qZ_S-ghoc
Data Model:
  • deleted all relationships auto-generated by Power BI
  • arranged tables into “waterfall” layout, with lookup tables at top, fact table at bottom, measure tables at top-right, and supporting tables at bottom-left
  • created 1-to-many relationships:
    • Account Managers[Account Manager ID] --> Transactions[Account Manager ID]
    • Buyers[Buyer ID] --> Transactions[Buyer ID]
    • Collection Types[Collection Type ID] --> Transactions[Collection Type ID]
    • Customer Load Types[Customer Load Type ID] --> Transactions[Customer Load Type ID]
    • Grades[Grade ID] --> Transactions[Grade ID]
    • Haulers[Hauler ID] --> Transactions[Hauler ID]
    • Process Flow Stages[Process Flow Stage ID] --> Transactions[Process Flow Stage ID]
    • Suppliers[Supplier ID] --> Transactions[Supplier ID]
    • Dates[Date] --> Transactions[Estimated Load Date] (inactive)
    • Dates[Date] --> Transactions[Confirmed Load Date] (inactive)
    • Dates[Date] --> Transactions[Delivery Date] (inactive)
    • Dates[Date] --> FX[Date]
    • Currencies[Currency Code] --> FX[Base Currency Code] (inactive)
    • Currencies[Currency Code] --> FX[Foreign Currency Code] (inactive)
    • Currencies[Currency Code] --> Transactions[Purchase Currency Code] (inactive)
    • Currencies[Currency Code] --> Transactions[Sale Currency Code] (inactive)
    • Currencies[Currency Code] --> Transactions[Haulage Currency Code] (inactive)
    • States[State ID] --> Transactions[Transaction State ID]
DAX Calculations:
  • added “Admin” measures for:
    • [Is Desktop] using difference between NOW() and UTCNOW() to determine user interface
    • [Is Service] using difference between USERNAME() and USERPRINCIPALNAME() to determine user interface
    • [… Tooltip] measures to use [Is Desktop] and [Is Service] to display the appropriate tooltip regardless of interface
  • formatted all DAX measures using Tabular Editor’s built-in formatter as outlined in YouTube video https://www.youtube.com/watch?v=oF0EbgKfEDY

foreach (var m in Model.AllMeasures) {
    m.Expression = FormatDax(m.Expression);
}

(Saved script as a custom action in Tabular Editor to facilitate reuse in other models.)


foreach (var m in Model.AllMeasures) { 
	m.Description = m.Expression; 
}

(Saved script as a custom action in Tabular Editor to facilitate reuse in other models.)

  • used measure to calculate the purchase amount in the base currency:

Purchase Amount (Base Currency) = 
VAR _TxDate = MAX( Transactions[Load Date] )
VAR _TxCurrencyCode = MAX( Transactions[Purchase Currency Code] )
VAR _TxAmount = MAX( Transactions[Purchase Weight] ) * MAX( Transactions[Unit Purchase Price] )
// get the exchange rate to the base currency for the transaction currency for the transaction date
VAR _ExchangeRate = LOOKUPVALUE( FX[Exchange Rate], FX[Date], _TxDate, FX[Foreign Currency Code], _TxCurrencyCode )
VAR _BaseAmount = _TxAmount * _ExchangeRate

RETURN
_BaseAmount

  • used measure to calculate the purchase amount in the display currency:

Purchase Amount (Display Currency) = 
VAR _DisplayCurrencyCode = SELECTEDVALUE( 'Display Currencies'[Currency Code] )
VAR _TxDate = MAX( Transactions[Load Date] )
VAR _TxCurrencyCode = MAX( Transactions[Purchase Currency Code] )
VAR _TxAmount = MAX( Transactions[Purchase Weight] ) * MAX( Transactions[Unit Purchase Price] )
// get the exchange rate to the base currency for the transaction currency for the transaction date
VAR _BaseExchangeRate = LOOKUPVALUE( FX[Exchange Rate], FX[Date], _TxDate, FX[Foreign Currency Code], _TxCurrencyCode )
// get the exchange rate to the display currency for the base currency for the transaction date
VAR _DisplayExchangeRate = LOOKUPVALUE( FX[Exchange Rate], FX[Date], _TxDate, FX[Foreign Currency Code], _DisplayCurrencyCode )
VAR _BaseAmount = _TxAmount * _BaseExchangeRate
VAR _DisplayAmount = DIVIDE( _BaseAmount, _DisplayExchangeRate, 0 )

RETURN
IF( _DisplayCurrencyCode = [Base Currency Code], _BaseAmount, _DisplayAmount )

  • created similar measures for Sale Amount
  • *** IN PROGRESS - INCOMPLETE *** create similar measures for Haulage amount
  • created [Is Filtered] measure to check all slicers and return 0 if no slicer selections had been made, 1 if slicer selection had been made (I’m sure there’s a better way to do this, but this was my first thought):

Is Filtered = 
VAR _StartAvailableDate = CALCULATE( MIN( Dates[Date] ), ALL( Dates[Date] ) )
VAR _StartSelectedDate = CALCULATE( MIN( Dates[Date] ), ALLSELECTED( Dates[Date] ) )
VAR _EndAvailableDate = CALCULATE( MAX( Dates[Date] ), ALL( Dates[Date] ) )
VAR _EndSelectedDate = CALCULATE( MAX( Dates[Date] ), ALLSELECTED( Dates[Date] ) )
VAR _DatesFiltered = IF( OR( _StartSelectedDate > _StartAvailableDate, _EndSelectedDate < _EndAvailableDate), 1, 0 )
VAR _BuyersAvailableCount = CALCULATE( COUNTROWS( Buyers ), ALL( Buyers ) )
VAR _BuyersSelectedCount = CALCULATE( COUNTROWS( Buyers ), ALLSELECTED( Buyers ) )
VAR _BuyersFiltered = IF( _BuyersSelectedCount < _BuyersAvailableCount, 1, 0 )
VAR _SuppliersAvailableCount = CALCULATE( COUNTROWS( Suppliers ), ALL( Suppliers ) )
VAR _SuppliersSelectedCount = CALCULATE( COUNTROWS( Suppliers ), ALLSELECTED( Suppliers ) )
VAR _SuppliersFiltered = IF( _SuppliersSelectedCount < _SuppliersAvailableCount, 1, 0 )
VAR _HaulersAvailableCount = CALCULATE( COUNTROWS( Haulers ), ALL( Haulers ) )
VAR _HaulersSelectedCount = CALCULATE( COUNTROWS( Haulers ), ALLSELECTED( Haulers ) )
VAR _HaulersFiltered = IF( _HaulersSelectedCount < _HaulersAvailableCount, 1, 0 )
VAR _GradesAvailableCount = CALCULATE( COUNTROWS( Grades ), ALL( Grades ) )
VAR _GradesSelectedCount = CALCULATE( COUNTROWS( Grades ), ALLSELECTED( Grades ) )
VAR _GradesFiltered = IF( _GradesSelectedCount < _GradesAvailableCount, 1, 0 )
VAR _AccountManagersAvailableCount = CALCULATE( COUNTROWS( 'Account Managers' ), ALL( 'Account Managers' ) )
VAR _AccountManagersSelectedCount = CALCULATE( COUNTROWS( 'Account Managers' ), ALLSELECTED( 'Account Managers' ) )
VAR _AccountManagersFiltered = IF( _AccountManagersSelectedCount < _AccountManagersAvailableCount, 1, 0 )
VAR _CollectionTypesAvailableCount = CALCULATE( COUNTROWS( 'Collection Types' ), ALL( 'Collection Types' ) )
VAR _CollectionTypesSelectedCount = CALCULATE( COUNTROWS( 'Collection Types' ), ALLSELECTED( 'Collection Types' ) )
VAR _CollectionTypesFiltered = IF( _CollectionTypesSelectedCount < _CollectionTypesAvailableCount, 1, 0 )
VAR _CustomerLoadTypesAvailableCount = CALCULATE( COUNTROWS( 'Customer Load Types' ), ALL( 'Customer Load Types' ) )
VAR _CustomerLoadTypesSelectedCount = CALCULATE( COUNTROWS( 'Customer Load Types' ), ALLSELECTED( 'Customer Load Types' ) )
VAR _CustomerLoadTypesFiltered = IF( _CustomerLoadTypesSelectedCount < _CustomerLoadTypesAvailableCount, 1, 0 )
VAR _ProcessFlowStagesAvailableCount = CALCULATE( COUNTROWS( 'Process Flow Stages' ), ALL( 'Process Flow Stages' ) )
VAR _ProcessFlowStagesSelectedCount = CALCULATE( COUNTROWS( 'Process Flow Stages' ), ALLSELECTED( 'Process Flow Stages' ) )
VAR _ProcessFlowStagesFiltered = IF( _ProcessFlowStagesSelectedCount < _ProcessFlowStagesAvailableCount, 1, 0 )
VAR _Result = SWITCH( TRUE(),
    _DatesFiltered > 0, 1,
    _BuyersFiltered > 0, 1,
    _SuppliersFiltered > 0, 1,
    _HaulersFiltered > 0, 1,
    _GradesFiltered > 0, 1,
    _AccountManagersFiltered > 0, 1,
    _CollectionTypesFiltered > 0, 1,
    _CustomerLoadTypesFiltered > 0, 1,
    _ProcessFlowStagesFiltered > 0, 1,
    0
)

RETURN
_Result

Visualizations:

General:

  • chose recycle and filter (funnel) icons from https://www.flaticon.com/;
  • added “bounce” to funnel icon by setting transparencies (default = 25%, on hover = 0%)

Landing:

  • used custom page size of 640 x 360 to be small and mimic the appearance of a splash page
  • smart narrative title using measures for [Report Name], [Company Name], and [Company Acronym]
  • recycle image
  • full-page button to navigate to summary page (this makes the [Landing] page only accessible on first opening in the Power BI Service

Summary:

  • recycle image
  • smart narrative title using measures for [Report Name], [Company Name], and [Company Acronym]
    • smart narrative title also uses [Is Filtered] measure (see above) to add “(filtered)” suffix if a slicer selection has been made
  • “funnel” button to navigate to [Filter] page
  • copied all slicers from [Filter] page; set all to sync and hid all
  • matrix visual listing slicer selections: date range, vendors (Buyers, Suppliers, Haulers), and others (Grades, Account Managers, Collection Types, Customer Load Types, Process Flow Stages), with “Filter \ Values \ Show on rows” enabled
  • last refresh smart narrative textbox using [Last Refresh] measure in bottom-right corner of page

Filter:

  • “Back to Summary” button to navigate back to the Summary page (icon left, text right)
  • “Reset Filters” button and bookmark to restore slicers to their default state (icon right, text left)
  • added “bounce” to “Back to Summary” and “Reset Filters” buttons (default - icon weight = 2, text font = Segoe UI, transparent background; on hover - icon weight = 4, text font = Segoe (Bold), fill 0% transparency, theme 3 blue background)
  • standard date slicer complete with slider
  • standard slicer drop downs for all dimensions (buyers, suppliers, … etc.)
  • standard matrix visual listing all slicer selections, with “Filter \ Values \ Show on rows” enabled
7 Likes

@Neba
For me, this has been your best report so far. I love the way you distinguished different currencies with different colors. The navigation, tooltips and color theme everything fits perfectly well in the report let alone the analytical side.
Really well done :clap:

@BrianJ,
Thank you for your kind words. It means a lot.
It was indeed a complex dataset and requirement.
I had a headache at some point trying to figure out the calculations.
Thanks for the recommendations , I will remember to include clear filters on my future reports.

1 Like

Thank you for your positive feedback @MudassirAli, I really appreciate it.

1 Like

Alright everyone, here’s my submission for data challenge 9!

I should start off by saying that unfortunately I didn’t include any machine learning in this report as I did last time, I’ll explain why at the end. Also, I didn’t really have time for this challenge as I only started this over the weekend haha.

Anyways, I did still take a different approach to this challenge as many others. Almost the entire report is dynamic. The main goal was to try to give the user the ability to determine what they wanted to see. A user can chose to see the any of the measures and how they are doing compared to the historicals in terms of MTD Current Month vs. Previous Month, MTD Current Month vs. Previous Year, or YTD Current Year vs. Previous Year. The currency selector is located just below the filters on each page with the current exchange rates used for that period listed at the bottom right of the page.

The most dynamic pages in the report though are definitely the last two (Top Performers & Adhoc Analysis). This one not only has all the features of the previous pages, but the user can see which dimension they want to see the data in and by which measure.









Machine Learning - As for machine learning, as my last challenge showed and were probably hoping to see some more of this time, I really like integrating machine learning into my reports, but this report was truly more focused on on the reporting side by providing a report for Enterprise DNA Recycle LTD (EDR) that allows everyone to be able to compare the same data universally across the company while being flexible enough to report any of the currencies. As a result, I didn’t really see anywhere from my perspective that machine learning would have been a value add for this report. Although I debated including a page that would go into forecasting exchange rate prices so that they could look at leveraging currency futures and save money or even make money based on that… If anybody would like me to create that page or walk through how to do that I’d be more than willing.

And that’s my report! I really tried for flexibility this time rather than my usual approach, but I think it turned out alright.

Update: Here’s the report published link (thank you @haroonali1000 for publishing this for me!):

12 Likes

Here is my submission for #9!

Including tooltips, currency change overlay, and the left slicer panel.

13 Likes

@bradsmith Amazing report I must say. Especially love the historical analysis that gives the user to see what has been happening in the past with unique visualizations. You have used many custom visuals that fit perfectly here. Did you use Zebra BI 4.4?

2 Likes

@datazoe Wow again. You included almost every detail in the report but the main highlight for me is the summaries (especially the one with Whisker Boxes) to detect outliers and the summary analysis is just superb.
Am I sensing a winner here?

2 Likes

@datazoe,

What a fascinating report. When I first looked at it, I was struck by the minimal design – no title bar, spartan black-and-white theme, no obvious slicers except for the currency choice. However, this initial impression is almost ironic, because as you continue to explore this report it opens up into something much more complex and detailed, revealing hidden slicers, subtle menus, vertical Sankey charts (!!), incredible multi-visual tooltips and a wonderful depth of analysis. It’s like walking through the door of a small, tidy house and somehow finding a mansion inside. It’s really like nothing I’ve seen before.

Amazing stuff. :clap: :clap:

  • Brian

PS – the gallery of your work that you get to by clicking on your logo is phenomenal. How do you do that auto zoom on hover effect?

2 Likes

This time I couldn’t complete the report and have been feeling like somebody robbed my valuables :pensive:.
I have been sick so I took Thursday and Friday off to spend time in creating the report. I completed the hard part that was the exchange rate calculations but have been sleeping majority of the time since Friday.
Now looking at the reports really make me sick again. :expressionless:

2 Likes

@bradsmith,

Okay, the fact that you didn’t start this report until yesterday and were still able to produce something of this high quality frankly just hurts my feelings. :grinning:

Seriously though, there’s so much excellent analysis here. I particularly like the use of the small multiples and spark lines (what visual did you use to create the small multiples?) I think this report will shine even more when folks get to see it published in service and interact with it. I would typically offer to do so, but recently my tenant settings got changed and I temporarily lost new publish to web capabilities. (Is there anyone out there in Data Challenge Land who can publish this report?).

I also applaud your restraint in not trying to shoehorn machine learning into a context in which it doesn’t fit well. Sometimes the best use of the tool is keeping it in the belt…

My one constructive suggestion is that with the extent and depth of analysis present in this report, a dynamic summary of the key findings/takeaways/recommendations might be helpful in guiding the user through this.

Really well done.

– Brian

Hello Forum members,

Here’s my submission.

(First Screen)
Sales(grey line) and Profit(orange line) bar chart. I just wanted to see how are they related each other in daily basis as it is my first time business model with more sales leads to more negative profits.
There are three currency icons to switch.
*After I got professional advise from @alexbadiu I have modified chart title colour and main title alignment. Only quick small changes but this looks much nicer than before. Thanks to Alex.

(Table & chart icon)
Open table view by clicking the table icon and bar chart icon is for the slicer of this table.
Icon colour changes once open the charts and click the coloured icon to close the charts.
By doing this consumer does not need to move cursor far away every time they close.

(Truck icon)
Simple regression chart for Haulage and sales

(Len icon)
Another regression chart for sales and profits with having outliers.

Thank you so much for viewing my report.

All the best for the other participants submissions.

Regards,

Hideo

9 Likes

Haha for that last comment! Now you are not submitting, I may have a chance :wink:

In seriousness though, I am so sorry you are sick and I hope you are feeling better soon. Hope you get can lots of rest.

1 Like

@Hideo This report is fantastic (and I’m not just saying that because of the lovely radial bar chart)! I love how usable this design is! And the overlay of charts instead of drill through or tooltip, I really like – you never get “lost” in a report this way. Really amazing, great job!

1 Like

Aww thank you @BrianJ! I’ll let you in on a secret, I start out and I’m all, I’m going to keep this simple, then I find out more about the data that I want to include – so I start having to find unique solutions to more content! One of the things that I felt this challenge was begging for was custom tooltips. I didn’t like that the out of box tooltip for the converted charts wouldn’t show the right format. To show the right format I had to include the custom tooltip and that allowed for some deeper diving which also had to be concise as you cannot click on them. I was going to make them accessible via drill-through too, but I forgot!

Thank you for you compliment on my site! I use wix.com and while I do web dev too, I don’t have to as it has an amazing builder (which is great or I’d honestly never have a site). It is a feature of one of their widgets (?) that I use to build the portfolio.

@datazoe
Thank you for lovely feedback. As you are one of my favourite participants, I feel so happy today!
You brought so much fun to the forum and I always like it!
P.S. Your website looks always fantastic :blush:

2 Likes

@alexbadiu Wow this report is so cool! The navigation and the descriptive subtitles, paired together to just all walk the user through the data – and just a splash of blue to highlight where you are (like a you are here dot) – amazing!

1 Like

@MudassirAli

Sorry to hear and I hope you get better soon. It is a bit of lost for us without you participating the challenges so maybe you will submit later once you get better. There is no time limit for you :grinning:
P.S. Congrats for your video for charticulator. I will support your activity!

Thank you for your feedback @MudassirAli !
In order to get the green I started from the logo (which I wanted to be a bright yellow). I found similar colors for green (and pink, blue, red etc). I tried them all and finally decided to use the green. I then tweaked the green on my background until it looked as I wanted. I wanted to use a high contrast green to showcase the power of pre attentive signals. I do not have a color pallete per say, I just tried to use the colors where it is needed.

@alexbadiu,

This report does a fantastic job telling the story of the dynamics driving this business. I am really bummed that I couldn’t get the publish to web version working in my browsers, since I wanted to fully explore the deep multidimensional 3 x 3 x 3 structure (three currencies x three navigation levels x data/process/people ) on which you’d organized this report. From what I was able to see though, the report does draw a very clear line of sight from the analytical results to the final recommendations, and again demonstrates a signature of your reports, which is cutting through a complex analyses to highlight just the most important aspects, and making the fact that they are important obvious to the reader through things like position, color, and particularly in this report the creative use of negative space.

As always, the design is beautiful and fits the theme of the challenge so well – I felt like this was like looking into a complex financial terminal screen and that the use of the dark background was extremely effective. I also particularly liked the dumbbell chart and wondered if that was a custom visual or Charticulator creation?

Another in an incredible run of top-notch reports.

– Brian

1 Like