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

Category: Field Parameters

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!

New Feature – Simplify Reports with Field Parameters

Did you check out the May 2022 Power BI update? I am genuinely excited about this release! So many great features to check out. However, the standout favorite from this release is the introduction of field parameters. I started playing with it and quickly discovered it is possibly my favorite release this year!

To set the scene, let me give you a common scenario that I encounter. Everyone wants to sort and view their data their way. With various dimensions and measures, there are endless possibilities when it comes to designing your report. Let’s say I have three categories that I can use against six measures. I could end up creating 18 different visualizations to handle all of these combinations. But with field parameters, I can simplify that process!

Enable the Preview

At the time I am writing this article, field parameters are a preview feature. You need to enable the feature first in order to use it. Go to the files menu, select options and setting, and click on options. From there, you need to go to the preview features section and enable field parameters.

Enabling the field parameters preview feature

Finish enabling the feature by restarting Power BI. As a result you will be ready to use field parameters in your report!

Creating Field Parameters

If you follow my blog, you will find the process to create field parameters similar to what if parameters. To create a field parameter, go to the modeling tab and select field under the new parameter menu.

Adding a field parameter

Next, we give our parameter a name. To keep things basic, I am naming mine dimensions. Then I add any fields I want to use as a parameter. I set my order and click create when I am ready.

Setting up field parameters

Two new things appear. The most obvious is a slicer on the report canvas. But there is also a new table that appears in the field list. We will be using that with our visual in a moment.

New slicer and table in Power BI

With the field parameter in place, it is time to apply it to a visual. Using a simple column chart, I will put my parameter on the X Axis. Then I will add my revenue measure to the Y Axis. With this in place, I can quickly jump between my parameters.

By default, we see revenue by segment
We also can see revenue by product
And revenue by country

How cool is that?! We took one visual and made it easy to pivot around with different dimensions. Talk about providing context while managing your report canvas!

Do Measures Work As Well?

You bet! Similar concept applies here. You create a new field parameter like above and add measures. A new slicer is added. Once in place, I add it to my Y Axis and start manipulating my chart.

Adding measures as field parameters

And if you had a keen eye, you also might be wondering if you can do multi-select on the slicer. And if you guessed yes, then you were right!

Selecting multiple parameters on a chart

And if you wanted to go a step further, you could apply the same principle to a table or matrix visual too.

Field parameters applied to a table

Done well, you could essentially create a simple pivot table inside of Power BI that your users can manipulate with a few clicks.

The Use Case for Field Parameters

Plain and simple, the use case for field parameters is enabling you to scale visualizations with minimal effort. Based upon our three dimensions and six measures, we would need to find space for all of these charts. This solution simplifies the need to create space for all of these options.

Even without this feature, we could make something like this work. A couple of weeks ago I wrote an article on using bookmarks to accomplish this task. It works well, but field parameters make it faster and easier to maintain. If you need to make changes, you might spend additional time updating each individual visual.

You also could allow users to customize visuals in the Power BI Service. They could accomplish the same goal on their own, but there is one problem – most people don’t know how to do it! Pair that with an unfamiliar data model and it can become a recipe for disaster. This solution guides the user to use the right fields to simplify their experience.

Anything Else to Consider?

I think the only major thing left to think about is your slicer behavior. My measures were pretty similar, so it would be okay to allow the multi-select behavior. However, if I had a measure calculating a percentage, I might need to enforce a single select to avoid issues with the scale of values.

Personally, this is one of those simple enhancements that is really going to make an impact for me. How about you? Are you going to try out field parameters in your reports? Tell me in the comments below!

Powered by WordPress & Theme by Anders Norén