Forum Discussion

Vickar's avatar
Vickar
Icon for Advocate II rankAdvocate II
3 years ago
Solved

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 ...
Need Help
  • Vickar's avatar
    Vickar
    3 years ago

    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's avatar
Vickar
Icon for Advocate II rankAdvocate II
3 years ago

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