How can we help?
< All Topics
Print

Drill-Down with Different Graphs in Each Layer

In this article, we will explore how to create a dynamic drill-down effect in Power BI using the HTML VizCreator Cert and HTML VizCreator Flex custom visuals. This approach enables you to present different graph styles at each drill-down level, providing an engaging and interactive data visualization experience. 

 Native Power BI visuals do not support different graph types at different drill-down levels. However, with HTML VizCreator, you can have unique graphs for each level for better presentation and insights. For the purpose of this article, we will create a three levelled visual, with KPI Cards as level one, Table as level two and a Progress Bar Chart as level three.  

 

Level 1:

Level 2:

Level 3:

Setting Up CSS Styles

Defining CSS styles for each drill-down level is a good practice to ensure a visually appealing and distinct presentation. Here’s a breakdown of how CSS can be utilized for different levels:

  1. Level 1 (Country): Uses card layouts to display KPI metrics. The cards have shadows, transitions, and hover effects to enhance interactivity.
  2. Level 2 (City): Employs a table format, with specific styles for borders, padding, and alternating row colors to improve readability.
  3. Level 3 (Product): Features a custom table with progress bars to illustrate sales performance. CSS styles control the layout, font, and bar animation.

By setting up these styles, each drill-down level will have a unique and consistent look, improving user experience and data presentation.

 

Below you can find the full code.

CSS =

VAR var_level1 =

"

<style>
  .card-container {
    column-count: 1;
    column-gap: 20px;
  }
  .kpi-card {
    box-shadow: 0 4px 8px 0 rgba(0,0,0,0.2);
    transition: 0.3s;
    width: 50%;
    margin-bottom: 20px;
    padding: 20px;
    text-align: center;
    box-sizing: border-box;
    break-inside: avoid;
    page-break-inside: avoid;

  }
  .kpi-card:hover {
    box-shadow: 0 8px 16px 0 rgba(0,0,0,0.5);
  }
  .kpi-title {
    font-size: 24px;
    color: #333;

  }
  .kpi-values {
    margin-top: 15px;
    font-size: 18px;
  }
</style>
"

VAR var_level2 = "

<style>

    table {
        width: 100%;
        border-collapse: collapse;
        font-size: 20px;
        color: #333;
    }

    td, th {
        border: 1px solid #add8e6;
        padding: 8px;
        text-align: left;
    }

    th {
        background-color: #add8e6;
        color: white;
    }

    tr:nth-child(even) {
        background-color: #f2f2f2;
    }

</style>

"
VAR var_level3 = ".table-style {
    border-collapse: collapse;
    width: 100%;
    height: auto;
    font-family: Arial, sans-serif;
}

.colgroup-col-1 {
    width: 30%;
}

.colgroup-col-2 {
    width: 70%;
}

.row-style {
    border: none;
    border-collapse: collapse;
    vertical-align: middle;
}

.td-label {
    text-align: right;
    padding-right: 40px;
    padding-top: 10px;
    padding-bottom: 10px;
    font-size: 18px;
    color: #333;
}

.td-bar-container {
    padding: 10px 0;
}

.bar-container {
    display: flex;
    align-items: center;
    height: 30px;
    border-radius: 5px;
    overflow: hidden;
}

.bar-style {
    height: 100%;
    background-color: #c3aed6;
    color: white;
    display: flex;
    align-items: center;
    justify-content: center;
    border-radius: 5px 0 0 5px;
    font-weight: bold;
    font-size: 16px;
    transition: width 0.5s ease-in-out;
}
"

RETURN

IF(ISINSCOPE('d Country'[Country Name]),
var_level1,
IF(ISINSCOPE('d City'[City Name]), var_level2, var_level3))

 

The RETURN part of the measure defines when each level should be used. If we are looking at data based on Country, we use level 1, for City we use level 2 and for Product we use level 3.

Defining HTML Content

Using DAX to create HTML content for each drill-down level is a good way to ensure your visuals are both dynamic and responsive.

 

Here’s how HTML is utilized for different levels:

 

  1. Level 1 (Country):
  2. Displays key performance indicators (KPIs) in a card layout.
  3. Shows the country name, total sales, total costs, and profit/loss with conditional formatting.
  4. Level 2 (City):
    1. Presents data in a table format.
    2. Includes columns for city name, total sales, total costs, and profit with conditional formatting.
  5. Level 3 (Product):
    1. Uses a custom table with progress bars to illustrate sales performance.
    2. Displays product name and a bar representing sales relative to the maximum value.

By defining HTML content in this manner, you create an interactive and visually consistent drill-down experience in your Power BI reports.

Below you can find the full code. You will also notice that we start by defining some variables based on the data we have in our model. This allows for our content to be dynamic and easily filtered.

 

HTML =

VAR var_country =                     SELECTEDVALUE('d Country'[Country Name],"Total")
VAR var_city =                        SELECTEDVALUE('d City'[City Name],"Total")
VAR var_sales =                       FORMAT([Total Sales], "0,0")
VAR var_costs =                       FORMAT([Total Cost], "0,0")
VAR var_profit =                      FORMAT([Total Profit], "0,0")
VAR var_profit_or_loss =              IF([Total Profit]>=0, "Profit" , "Loss")
VAR var_profit_color =                IF([Total Profit]>=0, "green" , "red")

VAR var_level1 = "
<body>
  <div class='card-container';'>
    <!-- KPI Card 1 -->
    <div class='kpi-card'>
      <div class='kpi-title' style='font-weight: bold; text-decoration: underline;'>"&var_country&"</div>
      <div class='kpi-values'>
        Total Sales: <span style='color: black;'>"&var_sales&"</span><br>
        <span style='font-style: italic;'>Total Costs: </span> "&var_costs&"<br>
        <span style='color: "&var_profit_color&";'><strong>"&var_profit_or_loss&": "&var_profit&"
      </div>
    </div>
  </div>
</body>

"
VAR var_level2 =

IF(ISBLANK([Total Sales]), BLANK(),
"
<table>
    <tr>
        <td width='300px';>"&var_city&"</td>
        <td width='150px' style='text-align: right; padding-right: 20px;'>"&var_sales&"</td>
        <td width='150px' style='text-align: right; padding-right: 20px;'>"&var_costs&"</td>
        <td width='150px' style='text-align: right; padding-right: 20px;'><span style='color: "&var_profit_color&";'><strong>"&var_profit&"</td>
    </tr>
</table>

")
VAR var_height_row=                  "10%"
VAR var_height_td=                   "10%"
VAR var_font_size_total=             "14pt"
VAR var_font_color=                  "black"
VAR var_max_value =                  MAXX(SUMMARIZE(ALLSELECTED('f Sales'),'f Sales'[Product ID],"Total",SUM('f Sales'[Sales])),[Total])
VAR var_label_name=                  SELECTEDVALUE('d Product'[Product Name])
VAR var_bar_length=                  FORMAT((DIVIDE([Total Sales], var_max_value) * 100), "0,0")
VAR var_value_in_bar=                FORMAT([Total Sales],"0,0")
VAR var_column_1_percent=            "30%"
VAR var_column_2_percent=            "70%"
VAR var_bar_color =                  "#c3aed6"
VAR var_level3 =

IF(ISBLANK([Total Sales]),BLANK(),

"<table class='table-style'>
    <colgroup>
        <col class='colgroup-col-1' style='width:"& var_column_1_percent&";'>
        <col class='colgroup-col-2' style='width:"& var_column_2_percent&";'>
    </colgroup>
    <tbody>
        <tr class='row-style'>
            <td class='td-label' rowspan='3'>"&var_label_name&"</td>
            <td class='td-bar-container'>
                <div class='bar-container'>
                    <div class='bar-style' style='width:"& var_bar_length&"%; background-color: "&var_bar_color&"; font-size:"& var_font_size_total&";'>"&var_value_in_bar&"</div>
                </div>
            </td>
        </tr>
    </tbody>
</table>
")

RETURN
IF(
    ISINSCOPE('d City'[City Name]),
    var_level2,
    IF(
        ISINSCOPE('d Country'[Country Name]),
        var_level1,
        var_level3
    )
)

 

Creating an HTML Header Measure

To provide a header for your tables at different drill-down levels, you can define an additional DAX measure. This measure will ensure that appropriate headers are displayed depending on the scope of your drill-down.

This HTML header code dynamically adjusts the table headers for different drill-down levels in Power BI:

 

  1. Level 1 (Country):
    1. No headers displayed (blank).
  2. Level 2 (City):
    1. Displays a table header with columns for City, Total Sales, Total Costs, and Profit in EUR.
  3. Level 3 (Product):
    1. Displays a custom table header with columns for Product Name and Sales Performance.

 

Below you can find the full code.

 

HTML Header =
VAR var_level1 = BLANK()
VAR var_level2 =

"<table>
    <tr>
        <th width='300px'>City</th>
        <th width='150px'>Total Sales (in EUR)</th>
        <th width='150px'>Total Costs (in EUR)</th>
        <th width='150px'>Profit (in EUR)</th>
    </tr>
</table>"

VAR var_level3 = "<table class='table-style'>
    <thead>
        <tr class='header-row'>
            <th style='width: 28%; text-align: right; padding-right: 22px; padding-bottom: 22px;font-size: 22px;'>Product Name</th>
            <th style='width: 72%; padding-left: 15px; font-size: 22px; padding-bottom: 22px'>Sales Performance</th>
        </tr>
    </thead>
</table>
"

RETURN
IF(ISINSCOPE('d Country'[Country Name]),
var_level1,
IF(ISINSCOPE('d City'[City Name]), var_level2, var_level3))

 

Implementation in Power BI

 

  1. Create new measures: Add a new measure for CSS, HTML and HTML header by copying the codes above.
  2. Add the HTML VizCreator Cert or HTML VizCreator Flex to your report from the AppSource.
  3. Add the visual you imported from the AppSource to your report page.
  4. Drag the measures you created in their designated places in the visualization pane.

Tip: Add a slicer for Country to your report to use the dynamic functionality of the visuals.

Drill-down visuals using HTML VizCreator Cert and HTML VizCreator Flex in Power BI offer a powerful way to visualize data hierarchies dynamically. By following the steps outlined above, you can create interactive, customized visuals that enhance your data analysis and presentation capabilities.

Explore the capabilities of HTML VizCreator Cert and HTML VizCreator Flex to create more engaging and insightful Power BI reports.

 

Demo Report

Download PBIX

Of course, you can create any kind of table with the HTML VizCreator Visuals. It can also be used to create pixel-perfect documents that are based on data from Power BI. A while back, we hosted a webinar where we showed how to create perfect invoices in Power BI.