Power BI : Dynamic Colors for TopN values on Clutered Column Chart

[Vickar]

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 

[Vickar · Accepted 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"
    )

[MohTawfik]

@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

[Vickar]

@MohTawfik, appreciate your feedback. "Others" is a part of the requirement. cannot be eliminated.

[VijayP]

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

[VijayP]

@Vickar 

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

[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