so i’m going to try and use a data model based on the one in the pattern
3 Likes
I found a simple way to get the historical fx rate using Google sheets .
The GOOGLEFINANCE function can be used to provide historical exchange rates. It will list the rate at the end of each day, for a period specified by you. This could be for a single day, a week, a month, or longer.
To do this, click on an empty cell and type =GOOGLEFINANCE("Currency:USDGBP", "price", DATE(YYYY,MM,DD), DATE(YYYY,MM,DD) , where the first nested DATE function is the start date, and the second DATE function is the end date.
Replace YYYY with the year, MM with the month, and DD with the day for both nested DATE functions. You’ll also need to replace the currency codes to match the currencies you’re looking to exchange .
Finally, you can import this in power bi using web import option.
Interested to know other options also…
2 Likes
one thing that seems unclear is just WHEN the transaction happens… for the sake of moving forward I’m going to go with estimated load date = transaction date
because i need a transaction date to calculate the currency exchange
1 Like
Dear all,
Please find below my submission for Challenge 9. This time I went for a dark background and a lot of interactivity through buttons and tooltips. I also used custom visuals to provide additional insight.
I had some problems when trying to publish to web. The main buttons on the left side of the screen do no work on Google Chrome (they work perfectly fine on PBI Desktop). They work on Internet Explorer and mobile phone. What is even weirder is that on Internet Explorer the colors of my tooltips change. I tried to rework my submission in the last 2 hours, remade the buttons, tried bookmarks, tried also to change my buttons and use Page Navigation. It just doesn’t want to work. Maybe it is a temporary bug.
LATER EDIT
I found a workaround and now the buttons work as they should. You can fully access the report by using the new link below.
Best regards
Alex
Publish to Web Link
11 Likes
Hello Everyone,
Here is my submission for challenge 9.
Here is a link to the report.
Thanks and Regards,
Nebiyu
11 Likes
@alexbadiu
Stunning report. The visuals, the analysis and the color theme everything top class. How did you get such a bright green color?
I was also preparing the report using the same theme as yours.
1 Like
This is a fantastic report. All the regular participants in the challenge seem to be developing a personal “signature” that uniquely identifies their work. To me, your reports are always characterized by incredibly creative navigation. This is a complex dataset and detailed analysis, and I love the way your use of color combined with navigation easily guides the user through it. The base color theme is really attractive, and I love the monochromatic detail pages that so clearly indicate what currency is being focused upon. Combine that with your skilled use of tooltips and drillthrough and the whole report is just a pleasure to use.
I also think your choice of visuals is outstanding, and the use of the ribbon chart on the summary page is particularly effective. My only suggestion for improvement would be to add a “clear all filters” button on each page, since there are so many different ways for the user to slice the data, that it might be difficult for the user to get back to the base condition (I know that’s possible within the service, but not all users are aware of that).
An outstanding job on a difficult dataset! 
– Brian
1 Like
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:
- Icons made by Freepik from www.flaticon.com
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
- created reference of [RAW Transactions] as [Transactions]
-
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
- 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.)
- *** IN PROGRESS - INCOMPLETE *** used eDNA DAX Clean Up tool to format all DAX measures and calculated columns https://analysthub.enterprisedna.co/apps/dax
- added the DAX measure expression to the measure description for all measures using Tabular Editor as outlined in eDNA post Sneak Peak of a code inside a measure:
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
8 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
@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
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
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.
- 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 
.
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. 
2 Likes
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. 
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