Data Visualization Workout 005 - Hierarchy bar chart + TOPn selector

Workout 005 – Revenues & Gross profit – bar chart and TOPn selector
Difficulty rating: 4 out of 5

Intro note
This workout combines various visualization techniques and native functionalities (i.e. field parameters, numeric range parameter, custom sorting options). The most important part of the workout is practice - **entries does not have to include every element or functionality to be still considered as great **. Please pay attention to the list of functionalities (provided in the screenshoot and in published-to-web report), which includes ‘basic’ elements (1st-4th point), and supplemental (5th - 6th point) as well as one extra (7-10th point). If your goal is to incorporate points marked on yellow color (7-10th point) - I would kindly recommend to review them before executing green and blue points to provide proper plan and flow of your work.

A good starting point is to implement a bar chart with hierarchical structure (by business line by contractor) that displays only revenues. Realizing further points (blue/yellow) will provide additional occasion to practice using numeric range parameters (and field parameters as well), creating custom sorting orders and TOPn segmentation.

Following workout bases on the same data source as provided in workout 002 (Profitability - scatter chart) - if you already participated in the 2rd workout - please feel free to reuse your already transformed datasource / model / measures etc.

**Following workout does not require to use column ‘Units sold’ . Nevertheless, please feel free to use any columns and any data which is valuable from your perspective. Feel free to use your entries as a part of your project portfolio, if it’s instrumental in realizing your goals.

Goals

In this workout, using the supplied dataset and reviewing the provided interactive report (link below) we are going to replicate the following set of vizualizations (left section in a report screenshot), including the bar chart, slicer pane, and additional headers. Replicating the slicer (left bottom corner) is not required (slicer allows to change data label density when ‘Both metrics’ are displayed on a bar chart). Also, please feel free to add any extra visual if you would like to (provided that core visualizations are included in the first place).

005 - Workout - Contractor profitability (bar chart).xlsx (110.9 KB)

Core actions (practice) :

    • Making basic calculation (gross profit, gross margin)
    • Getting familiar with the bar chart in general (different formatting options, labels etc.)
    • Getting familiar with hierarchical structure of y-axis within the bar chart
    • Creating custom ranking / sorting formulas
    • Using numeric-range parameters and creating TOPn functionalities
    • Using field parameter to display preferable measures within a chart
    • Creating basic custom tooltips

Submission

Please load the supplied data (xls file) into a Power BI file, replicate visualizations (considerating list of points in the introductory report screenshot and the general design and functionality provided through interactive published-to-web report), and reply to this post including the screenshot (or a gif) of your solution along with the pbix file (please, include name or EDNA forum nick in the pbix name).

Period

This workout is released on Tuesday April 24, 2023, and the author’s solution will be posted on Monday/Tuesday May 1-2, 2023.

Additional materials

Link to the interactive dashboard:

Have a great fun!

Best regards!

4 Likes

I have been diligently working on that in anticipation of its impending arrival :smiley:

1 Like

Wow! Another well planned workout @Gustaw. This was loaded with complications. Because it took longer than a lunchbreak :wink: I still have several things to wire up. This includes the “meat” of the problem wrt ranking and sorting with field parameters.

gif-a

*** update:

All wired up now. Gif replaced.

Generating a single value to rank subcategories within levels of categories was more challenging than I anticipated. I had hoped to derive a measure from both the rank of business line (excluding customer) and the rank of the customer in something like {business line rank, customer rank}:

{1,1}, {1,2}, {1,3}, {1,4}, {1,5}   
{2,1}, {2,2}, {2,3}, {2,4}, {2,5}

but I struggled with calculating the rank of the business line when isinscope customer is true.

It was a really fun distraction @Gustaw. Thanks again.

3 Likes

Almost there

3 Likes

Looks not so bad in the picture, but … apart from this almost a disaster :frowning:

  • no idea how to solve issues with “ranked & sorted by”,
  • “display as - both metrics” does not work correctly - It sums both metrics but does not show it in 2 columns,
    These two issues probably can not solve without help :frowning:

Apart from this still needs to work on the bar chart header and disappearing labels. Hope to solve 2 issues I just need more time …

workout 005.pbix (99.6 KB)

2 Likes

It already looks great @renata_kulagowska !
Really great to see the TOPn parameter!

We will go through the rest soon!

Solutions are looking great. I am hoping to start tomorrow and it is forecast to be raining on Sunday so hoping I can complete at least up to Q6.

I like how we are re-using datasets as it really helps speed up the process.

2 Likes

Fantastic attempt. Well done

1 Like

Really well done. Great to see max effort on this workout

3 Likes

Hi there,
Could I get some help please? I am stuck on the chart. I have used a technique by Sam to get the TopN

Summary

This measure works fine on the chart if I only select client for y axis. If I select business line & client for y axis it won’t work :frowning: It does however work if I select year & client for y axis

Any tips that could point me in the right direction would be appreciated.

pbix is attached
Workout #5 Hierarchy Bar Chart TopN KimC v1.pbix (209.7 KB)

1 Like

Hi all,
This is my approach to WO5. Thank you Gustaw for this challenge. It’s “Think outside the box” for me. I have just started learning how to implement multi-level filtration in visualizations using DAX. It’s not easy, but I’m doing my best. Thank you for this opportunity.
Workout 005_EDNA_AngelinaMihejenkova.pbix (634.9 KB)
Workout 005_EDNA_AngelinaMihejenkova

3 Likes

Hi, here is my submission to the workout challenge
No idea about the sorting and getting the displayed metrics as both metrics working
Hope I did it justice


EDNA Visuals 005.pbix (451.3 KB)
Report Link

1 Like

I have worked this out :slight_smile:
If anyone else is stuck on this step, check this out:

1 Like

hi,
very strong workout !
I had a lot of troubles with the two level ranks (first by Business Line, second by Customer). In current pbix I used, as first level, a method based on an IF considering that Business lines are only two… but I am not fully happy…
I also see GM% and Profit% show incorrect values…
Anyway I enjoyed doing the full workout even if, every time I look at Gustaw’s viz, I discover something new.
Ciao !



Workout 005 - hierarchy bar chart and top N selector - Roberto Ravani.pbix (452.3 KB)

2 Likes

I “think”, to get both metrics to display at one time you need to use some data modelling.

I created two Field Parameter tbls, duplicated each measure in the table and created a master Param Filter table to filter down to either one measure or both. This also involves two of the parameter fields being placed in your values field.

This was how I approached it, not sure if it’s the best way to do it but it seems to work for me…now I just need to review my TopN measure as that is throwing out some funky numbers :rofl:

To be honest, I’m not 100% sure my method is the way to do it, just thought I’d post in case it helps others get a working solution.

image

5 Likes

oh, I see. Very creative.

2 Likes

This is the best I can do today. Happy with progress so far but a lot still to be done.

3 Likes

Looking v.good @KimC :+1:t2:

1 Like

Hello everyone,

Here is my submission for the workout challenge. This challenge was a tough one! I pushed myself to the limit and gave it my all but still I couldn’t complete all the elements such as sorting and displaying metrics.

Thanks again to @Gustaw for putting on this competition and giving me the opportunity to showcase my skills.

005 - Workout - Revenues & Gross profit – bar chart and TOPn selector_gif

Interactive report:

PBIx file:

Workout 005 - Revenues & Gross profit – bar chart and TOPn selector_.pbix (646.4 KB)

3 Likes

Hi @ErmanA,
It looks absolutely phenomenal, even without sorting order.
Appreciate your great effort put into this one!
Amazing work - great design, buch of functionalitites (hierarchy / tooltips / sorting by different metrics / headers etc).

Thank you for participation!!

1 Like