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:
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.
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.
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] DESCWith 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.
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.
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:
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:
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:
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!
