Making sense of challenging data topics one step at a time.

Tag: Data Visualization

Dynamically Compare Customers in a Report

I was working with a customer recently and she came up with an interesting use case with Power BI. The idea was simple – she needs to compare a manufacturer against the top three manufacturers within a period of time. Thinking it was pretty straight forward, I suggested she just use a bar chart and filter down to those manufacturers and call it a day. If only it were that simple!

She came back and said well I need to dynamically select the chosen manufacturers. And in a perfect world, I would like to change the threshold of top manufacturers. Naturally, this whole scenario became far more complex! Being someone who does not back down from a challenge, I decided to give it a shot!

To help break down how this will work, I decided to use DAX Studio. It is a free tool that you can download and install from here. This is not a prerequisite, but it does make explaining the measure easier.

Part 1 – Design the measure in DAX Studio

The first step in the process is that we have to build out our base query. To start, we will pull back a list of manufacturers and the revenue associated with them. We will return that query ordered by the manufacturer with the highest revenue at the top:

DAX Studio window with a list of manufacturers and their associated revenue totals.
List of all manufacturers and their associated revenue totals

Next, we need to limit the list to only the top number of manufacturers selected. We will add a variable with a hard coded value of three for now. However, we will make it dynamic later on. We also need to add a filter to our list of manufacturers so only the top three return. We will use RANKX() to achieve that result.

Query in DAX Studio updated to only return the top three manufacturers and their associated revenue totals.
Returning the top three manufacturers by revenue

Next, we want to declare a manufacturer and return the associated revenue. For now, we will hard code the manufacturer Fama and update it later. We will then return the revenue associated with our selected manufacturer.

Screenshot of DAX Studio showing the top three manufacturers revenue plus our revenue for Fama.
Final code from DAX Studio

If you are following along at home, the final code for what we put together in DAX Studio is as follows:

DEFINE
    VAR TopRank = 3
    VAR SelectedManufacturer = "Fama"
    VAR ListOfManufacturers =
        ADDCOLUMNS (
            SUMMARIZE ( bi_manufacturer, bi_manufacturer[Manufacturer] ),
            "Revenue", [Revenue]
        )
    VAR FilteredList =
        FILTER (
            ListOfManufacturers,
            RANKX ( ListOfManufacturers, [Revenue],, DESC ) <= TopRank
        )
    VAR SelectedManufacturerRevenue =
        CALCULATE ( [Revenue], bi_manufacturer[Manufacturer] = SelectedManufacturer )
    VAR UnifiedList =
        UNION (
            FilteredList,
            { ( SelectedManufacturer, SelectedManufacturerRevenue ) }
        )

EVALUATE
UnifiedList
ORDER BY [Revenue] DESC

With that set, we can now turn our attention back to Power BI to prepare our report for the next step.

Part 2 – Preparing Power BI

There are two elements we must prepare for this scenario. First, we must prepare an independent manufacturing list. To avoid any conflicts with filtering, we must create this as a new query that parallels our existing bi_manufacturing table and is used in our calculation.

Next, we will need to add our top manufacturer threshold selection. Right now we hard coded the value three in our formula, but we want it to be dynamic. We will use a what-if parameter to achieve that goal.

Preparing the manufacturer list

The next step in our process is to prepare a list of distinct manufacturers. You could choose to create a DAX table to achieve this, but I suggest performing this in Power Query. I simply referenced my bi_manufacturer table, removed my extra columns, and updated the name to ManufacturerList.

Image of my prepared manufacturer list inside of Power Query.
List of manufacturers in Power Query

Next, I will need to create a measure to identify the selected manufacturer. This will allow me to select my manufacturer from a list. And if a single value has not been selected, it will return a blank value instead.

Selected Manufacturer = SELECTEDVALUE(ManufacturerList[Manufacturer], "")

With all of this in place, we just need to add a slicer on our report page.

Screenshot of our Top N Compare report canvas with the Manufacturer slicer present and set to Fama.
Top N Compare report page with Fama selected from the ManufacturerList

With this in place, it is time to add our threshold measure.

Preparing the Top N selector

In the past, I wrote an article on leveraging What If Parameters in Power BI. In that scenario, we used a decimal value for percentages. Instead, we will use whole numbers for this situation. However, a small change has occurred since that article was published. Now, there are two types of parameters we can select. We will be selecting the numeric parameter to achieve our goal. Head to the modeling ribbon and select the numeric range option from the new parameter dropdown. You will need to update the highlighted fields below:

Screenshot of selecting and updating a numeric range parameter in Power BI.
Adding a numeric range parameter in Power BI

Leaving the add slicer option checked, click create. This will generate a new table and measure that will work in concert with the new slicer. We do not have to do anything extra at this point, but it is worth noting that you could choose to create these elements manually if you choose.

Part 3 – Modify DAX Studio code for use in Power BI

Time for the grand finale! With all our hard work, the last few steps are about making some small adjustments to our DAX Studio code. We will start by creating a new measure and adapting our code for use in Power BI.

Taking the code from above, the first step is to remove the word DEFINE from the block. Next, we will update our hard coded values for top rank and selected manufacturer. If you create a report measures table, note that the [Top N Manufacturer Value] will not appear there unless you move it. Check under that new table we created.

Next, we will add a new variable at the top called CustomerLine and use it to bridge our revenue to the right customer. The code will look like this:

VAR ManufacturerLine = MAX(bi_manufacturer[Manufacturer])

Lastly, we will change our EVALUATE to a RETURN and update the value to perform a SUMX() on the revenue of our unified list where the ManufacturerLine value matches the Manufacturer. Our final code for our measure should look like this:


VAR TopRank = [Top N Manufacturers Value]
VAR SelectedManufacturer = [Selected Manufacturer]
VAR ManufacturerLine =
    MAX ( bi_manufacturer[Manufacturer] )
VAR ListOfManufacturers =
    ADDCOLUMNS (
        SUMMARIZE ( bi_manufacturer, bi_manufacturer[Manufacturer] ),
        "Revenue", [Revenue]
    )
VAR FilteredList =
    FILTER (
        ListOfManufacturers,
        RANKX ( ListOfManufacturers, [Revenue],, DESC ) <= TopRank
    )
VAR SelectedManufacturerRevenue =
    CALCULATE ( [Revenue], bi_manufacturer[Manufacturer] = SelectedManufacturer )
VAR UnifiedList =
    UNION (
        FilteredList,
        { ( SelectedManufacturer, SelectedManufacturerRevenue ) }
    )
RETURN
    SUMX ( FILTER ( UnifiedList, [Manufacturer] = ManufacturerLine ), [Revenue] )

At this point, I added my Top N Revenue measure to my table to compare and I discovered two issues. One, it is returning revenue for every line, not just the Top N and the selected manufacturer. But in addition, it is doubling the revenue for Fama:

Screenshot of issues from our new Top N Revenue measure.
Top N Revenue measure not working properly

To fix every value coming back, we will simply add an ALL() around our original list of manufacturers. This will make sure all manufacturers are accounted for in this calculation:


VAR TopRank = [Top N Manufacturers Value]
VAR SelectedManufacturer = [Selected Manufacturer]
VAR ManufacturerLine =
    MAX ( bi_manufacturer[Manufacturer] )
VAR ListOfManufacturers =
    ADDCOLUMNS (
        SUMMARIZE ( ALL ( bi_manufacturer ), bi_manufacturer[Manufacturer] ),
        "Revenue", [Revenue]
    )
VAR FilteredList =
    FILTER (
        ListOfManufacturers,
        RANKX ( ListOfManufacturers, [Revenue],, DESC ) <= TopRank
    )
VAR SelectedManufacturerRevenue =
    CALCULATE ( [Revenue], bi_manufacturer[Manufacturer] = SelectedManufacturer )
VAR UnifiedList =
    UNION (
        FilteredList,
        { ( SelectedManufacturer, SelectedManufacturerRevenue ) }
    )
RETURN
    SUMX ( FILTER ( UnifiedList, [Manufacturer] = ManufacturerLine ), [Revenue] )

To avoid a potential collision where our selected manufacturer is also in our top list, we just need to add some logic in our return statement to see if our manufacturer is present. If it is, simply return the value from the top list and move on.

VAR TopRank = [Top N Manufacturers Value]
VAR SelectedManufacturer = [Selected Manufacturer]
VAR ManufacturerLine =
    MAX ( bi_manufacturer[Manufacturer] )
VAR ListOfManufacturers =
    ADDCOLUMNS (
        SUMMARIZE ( ALL ( bi_manufacturer ), bi_manufacturer[Manufacturer] ),
        "Revenue", [Revenue]
    )
VAR FilteredList =
    FILTER (
        ListOfManufacturers,
        RANKX ( ListOfManufacturers, [Revenue],, DESC ) <= TopRank
    )
VAR SelectedManufacturerRevenue =
    CALCULATE ( [Revenue], bi_manufacturer[Manufacturer] = SelectedManufacturer )
VAR UnifiedList =
    UNION (
        FilteredList,
        { ( SelectedManufacturer, SelectedManufacturerRevenue ) }
    )
RETURN
    IF (
        SUMX ( FILTER ( FilteredList, [Manufacturer] = ManufacturerLine ), [Revenue] ) > 0,
        SUMX ( FILTER ( FilteredList, [Manufacturer] = ManufacturerLine ), [Revenue] ),
        SUMX ( FILTER ( UnifiedList, [Manufacturer] = ManufacturerLine ), [Revenue] )
    )

And the results speak for themselves:

Screenshot of the Top N Revenue values corrected.
Top N Revenue working properly

Is it a lot of work to enact? Yes. But is there an opportunity to extract valuable insights from this type of report? Also yes.

Where to go next?

If you want to take this to the next level, consider adding some conditional formatting. Go back and read my article on creating conditional formatting with a column chart. This will allow you to call out your selected manufacturer a lot easier. It is a small adjustment, but it could go a long way to enhancing your report design.

It is worth considering that my queries to achieve this outcome were very simple. Depending on what measures you are comparing, this could become a very cumbersome solution. It is important that you take the time to review your report’s performance and ensure it does not overwhelm your capacity in the Power BI Service. Use the performance analyzer and continue to tune your measures to ensure no issues with rendering this data.

Conclusion

This is a bit of a challenging project. Even while writing this one, I was getting exhausted. It seems simple in practice, but it does require a lot of effort to achieve this desired outcome. I hope if you run into a scenario where you have this requirement that you will find this solution helpful!

So, have you seen anything like this before? Have you run into challenges like this? If so, make sure to share in the comments below!

Conditional Formatting – Moving to the Next Level

Last week, I went through the basics of conditional formatting. I shared how it helps take your reports to the next level and the steps required to use it. We looked at gradient and rule based conditional formatting but did not touch the field option. I wanted to split this one out because it can be a little more complicated but is quite versatile.

What is Field Based Conditional Formatting?

Isn’t all conditional formatting based upon fields? Of course it is! We are always looking at field values to activate conditional formatting. Field based conditional formatting is not looking at the field values for what needs to happen. Instead, it is looking to the field for what formatting is supposed to appear.

Let’s use our example from last week where we looked at revenue versus goals. We used a simple set of rules to highlight table rows as red, yellow, or green based on values.

Enabling Rule Based Conditional Formatting

Instead of setting a bunch of rules, we can create a measure that specifies these colors. If you remember a few weeks ago, I did a post on using the SWITCH() function in DAX. I am going to use it to create a measure to specify the colors I want to use.

Field Formatting v1 = 
VAR SumRevenue = SUM(Revenue[Revenue])
RETURN
    SWITCH(TRUE(),
        SumRevenue < 1450000, "Red",
        SumRevenue < 1500000, "Yellow",
        SumRevenue >= 1500000, "Green",
        "Gray")

Note that I am using colors by name in my measure. It is not fancy, but it gets the job done. Once the measure is ready, we just need to apply it to the column. I select field as my format type and then select the measure I just created.

Setting Up Field Based Conditional Formatting

The result is my table formatted as expected.

Results from Field Based Conditional Formatting

And just like that we are using a measure to drive our conditional formatting.

This Looks Great! Can I Customize My Colors?

You can absolutely customize your colors! Instead of writing out the words, you can use hex codes to set the color.

Field Formatting v2 = 
VAR SumRevenue = SUM(Revenue[Revenue])
RETURN
    SWITCH(TRUE(),
        SumRevenue < 1450000, "#9c0a00",
        SumRevenue < 1500000, "#f7da00",
        SumRevenue >= 1500000, "#006616",
        "Gray")

Using a branding guide to help me, I pulled the red, yellow, and green hex codes to use in my report.

Field Based Conditional Formatting Using Hex Color Codes

But now my colors are a little too dark How do I fix that? Here’s a little pro tip – you can add two numbers on the end of your hex code to determine the transparency of the color. I wanted to soften the colors a bit and make it 50% transparent, so I added a 50 to the end of each hex code.

Field Formatting v3 = 
VAR SumRevenue = SUM(Revenue[Revenue])
RETURN
    SWITCH(TRUE(),
        SumRevenue < 1450000, "#9c0a0050",
        SumRevenue < 1500000, "#f7da0050",
        SumRevenue >= 1500000, "#00661650",
        "Gray")

And the difference is dramatic!

This makes a huge difference in our formatting and it is much easier to read.

Why Go Through All Of This Effort For Conditional Formatting?

If you are anything like me, you are probably wondering why you should go through this kind of effort to build out measures for conditional formatting. Every tool has a purpose and this is one that rarely gets leveraged. But consider some of these use cases where you might find it useful.

Complex Rules

When using rules, you find that you are put into a simple set of conditions that drive your formatting. Maybe you have a complex set of conditions that you need to consider. Instead of a simple greater than, less than, or between set of rules, you can build out some complex logic.

For example, you only want the colors to appear for the months in Q4. You cannot accomplish this with the standard set of rules. But with a little extra DAX, you can make that happen.

Field Formatting v4 = 
VAR SumRevenue = SUM(Revenue[Revenue])
RETURN
    SWITCH(QUARTER(MAX(Revenue[Month])) = 4,
    TRUE(), 
        SWITCH(TRUE(),
            SumRevenue < 1450000, "#9c0a0050",
            SumRevenue < 1500000, "#f7da0050",
            SumRevenue >= 1500000, "#00661650",
            "Gray")
    )

If you wanted to get really crazy, you can build out a complex measure that darkens the color the closer you get to the due date of a goal. The sky is the limit!

Color Flexibility

What if you had a report that can be filtered for multiple customers? You could setup your formatting to highlight values using their primary color color scheme.

If you have a customer table, you can specify some key palette colors and reference them in your SWITCH() statement. This enables a personal touch for each customer without having to create separate report pages or files for each one. Talk about scaling out your reporting!

Anything Else I Need To Know?

Remember, you are building out your conditional formatting using measures. You always want to optimize them to be efficient and not tax your report canvas. You are adding additional calculations to your report canvas, so don’t overdue it!

So how about you? Have you used field based conditional formatting before? If not, do you have a use case? If so, tell me about it in the comments below!

Welcome and Hello!

Thank you for visiting! I am excited to share my thoughts and experience around all facets of data. I started this journey because of the frustration I often see others suffer from every time they try to effectively use data. My goal is to share some of the tips and tricks I have picked up along the way with tools like Power BI and Excel to simplify the process.

I also am looking forward to sharing my thoughts on the state of data literacy. It has an impact on the readability of content created and the consumers who view it. Frequently I find horrible examples of data visualization out in the wild. Sadly, I see these examples being produced by organizations who claim to hold themselves to high standards. I want to bring these examples to light, dissect them, and show you how to improve them. Report viewers do not always think about these things. Together, we can change how content creators put together reports that are more accessible by consumers.

So thank you for joining me here and letting me share my stories! I look forward to us both learning together!

Powered by WordPress & Theme by Anders Norén