Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Vickar
Advocate I
Advocate I

Power 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
Visual breaks if less than 5 values are selected.png

here is the link to the pbix file 

1 ACCEPTED SOLUTION
Vickar
Advocate I
Advocate I

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"
    )

 

 

View solution in original post

7 REPLIES 7
Vickar
Advocate I
Advocate I

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"
    )

 

 

MohTawfik
Resolver I
Resolver I

MohTawfik_0-1692534798691.png

@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.

VijayP
Super User
Super User

@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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

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.

VijayP
Super User
Super User

@Vickar 

Look at the change in TOPN formula , i have changed the number 6 with a slicer value




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.