Forum Discussion
Advocate IIPower BI : Dynamic Colors for TopN values on Clutered Column Chart
Requirement: Assign dynamic colors to the subcategory values on the clustered column chart.
Background: The chart shows Top 6 subcategories by SalesAmount and rest other categories as Others. There is a filter on subcategory in the filter pane on All Pages level
Partial Solution & the Problem: I have created a measure [SelectedSubcategoryColor] that does returns dynamic colors but it only works when more than 5 subcategories are selected. If less than 5 subcategories are selected, then the visual breaks. I want the visual to work even if 1 subcategory is selected
Below is the screenshot of the problem
here is the link to the pbix file
shout-out to Amira Bedhiafi for providing the solution on stackoverflow
Below is the DAX solution to the problem in the [SelectedSubcategoryColor] measureSelectedSubcategoryColor = VAR _CurrentSubcategory = SELECTEDVALUE ( Subcategory[SubcategoryName] ) VAR _AllSubcategories = ALLSELECTED( Subcategory ) VAR _CurrentRank = RANKX( _AllSubcategories, [Sales], , DESC ) RETURN SWITCH( TRUE(), _CurrentRank = 1, "Red", _CurrentRank = 2, "Green", _CurrentRank = 3, "Blue", _CurrentRank = 4, "Orange", _CurrentRank = 5, "Purple", _CurrentRank = 6, "Yellow", _CurrentSubcategory = "Others", "Grey", "Black" )
7 Replies
- MohTawfik
Resolver I
Vickar I've done a different solution using ranking, getting rid of "others"
check the attached file!
hope this solves your issue
Dynamic Colors for TopN values on Column Chart.pbix 
Vickarshout-out to Amira Bedhiafi for providing the solution on stackoverflow
Below is the DAX solution to the problem in the [SelectedSubcategoryColor] measureSelectedSubcategoryColor = VAR _CurrentSubcategory = SELECTEDVALUE ( Subcategory[SubcategoryName] ) VAR _AllSubcategories = ALLSELECTED( Subcategory ) VAR _CurrentRank = RANKX( _AllSubcategories, [Sales], , DESC ) RETURN SWITCH( TRUE(), _CurrentRank = 1, "Red", _CurrentRank = 2, "Green", _CurrentRank = 3, "Blue", _CurrentRank = 4, "Orange", _CurrentRank = 5, "Purple", _CurrentRank = 6, "Yellow", _CurrentSubcategory = "Others", "Grey", "Black" )- Vickar
VijayP appreciate your feedback. The problem persists though. If I select less than 5 subcategories from the filter pane, the visual still breaks. Also, as part of the requirement, the visual should show max 7 data elements, 6 topn and rest as others. So the topn shouldn't come from the slicer, it should be static value as 6
- VijayP
Community Champion
Vickar As per your problem statement , you are trying to select less than 6 , when it has to be static what is the purpose of reducing the number of elements.
The example which I shared is working with all slicer values even when I select 1 it shows 1 value + others
https://drive.google.com/file/d/1CaSISzxacCwRldbqx7PLo1nyckrprPXk/view?usp=sharing
- Vickar
the requirement is to show max 7 data elements on the visual if more than 6 subcategories are selected ( 6 topn and rest as others )
And it is a valid use case that the user can select less than 6 subcategories. the issue is not with [TopNSubcategorySales] measure. it is working fine for all scenarios. the issue is with the [SelectedSubcategoryColor] measure that assigns colors dynamically to the subcategories on the visual. this measure is breaking the visual if less than 5 subcategories are selected. I hope the issue is clearer now. Appreciate your efforts and support. And I double checked your solution, the visual is still breaking if I select less values in hte filter pane.
