Analytics: Power BI
Dynamic Legend in Power BI
Dynamically Change Legends on Column Charts Using Slicer to Show Composition of Value Using Different Dimensions
Have you ever needed to present your quarterly revenue broken down by different dimensions? Power BI’s native legend options for column charts allow you to use only a single dimensional column for the legend. But what if your users want to explore revenue composition using various attributes? This is where Power BI’s powerful Field Parameter feature comes into play.
Let’s explore how to implement this solution:
Requirement:
Imagine you have sales data that details revenue across two attributes:
- Five “Sport” categories: Football, Badminton, Tennis, Cycling, and Swimming.
- Five “Accessories” categories for each sport category.
- Additionally, data is available for two quarters, Q1 and Q2.
The goal is to present the composition of revenue as a percentage from each component or attribute, represented by different color stacks.
Solution:
First, we need to create a measure that will display the desired value. Our objective is to show the percentage of total revenue contributed by each sport category for each quarter, followed by the percentage of total revenue contributed by each accessory category. We achieve this with the following DAX expression:
Revenue Percentage =
VAR __Numerator =
CALCULATE(
SUM(Fact_SportsSales[Revenue]), KEEPFILTERS(Fact_SportsSales[Revenue])
)
VAR __Denominator =
CALCULATE(
SUM(Fact_SportsSales[Revenue]), ALL(Fact_SportsSales)
)
RETURN
DIVIDE(__Numerator, __Denominator, BLANK())The above DAX expression fulfills the example’s requirements. However, you can replace it with any other calculation measure based on your specific needs. Next, create a Stacked Column Chart, placing “Quarter” on the X-axis and the newly created measure “Revenue Percentage” on the Y-axis.
Now comes the most important part — Create a Field Parameter from Modelling Menu -> Parameter and add the two attributes “Sport” and “Accessories” in the field parameter in the exact order. Finish it up by giving the Field Parameter the desired name “Categories”.
After completing this step, a slicer will appear with the two attributes you selected as part of the “Categories” Field Parameter. Adjust the slicer appearance and selection criteria to fit your needs. In this example, we chose to display the slicer as a list with a single-select radio button.
Next, drag the “Categories” Field Parameter column into the “Legend” well of the Stacked Column Chart. Now, depending on the slicer selection, the chart will display columns stacked with different colors representing the selected categories.
Up next, drag the Field Parameter “Categories” column into the “Legend” well of the Stacked Column chart.
Now you should be able to see the columns of the chart stacked with different colored categories as per the selection of the slicer.
Results:
Now it’s time to see your solution in action! When the user selects the “Sport” category from the slicer, the chart will show the revenue distribution by sport categories for each quarter.
By toggling the slicer to “Accessories,” the chart will now display the revenue composition by accessories for each quarter.
By leveraging Power BI’s Field Parameter feature, you can create dynamic and interactive visuals that allow users to explore data from multiple perspectives effortlessly. This approach not only enhances the user experience by providing flexibility but also enables more insightful analysis by presenting data in a clear and adaptable manner. Whether it’s breaking down revenue by different categories or comparing other dimensions, this solution offers a powerful way to visualize complex data. Implementing dynamic legends in your reports can significantly improve the way stakeholders engage with and interpret your data.
Thank you for reading!
Please feel free to clap 👏 if you found this article helpful and leave a comment or share it with others. Follow me for more insights on analytics. You can find me on LinkedIn.
Don’t forget to subscribe to
and join our Power BI community
