Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
shout-out to Amira Bedhiafi for providing the solution on stackoverflow
Below is the DAX solution to the problem in the [SelectedSubcategoryColor] measure
SelectedSubcategoryColor =
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"
)
shout-out to Amira Bedhiafi for providing the solution on stackoverflow
Below is the DAX solution to the problem in the [SelectedSubcategoryColor] measure
SelectedSubcategoryColor =
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 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
@MohTawfik, appreciate your feedback. "Others" is a part of the requirement. cannot be eliminated.
@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
Proud to be a Super User!
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.
Look at the change in TOPN formula , i have changed the number 6 with a slicer value
Proud to be a Super User!
@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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
107 | |
97 | |
38 | |
32 |
User | Count |
---|---|
153 | |
122 | |
77 | |
74 | |
44 |