Create colors based on value, not category

[jaryszek]

Hello,

my model looks like:

and what i want to do is to have the same colors for the top 5 highest value category (not categories which leads to ugly looking reports).

I tried to add:

Rank (Current Axis) :=
VAR CatTable =
    SWITCH (
        TRUE(),
        ISINSCOPE ( Fct_EA_AmortizedCosts[MeterSubCategory] ),
            ALLSELECTED ( Fct_EA_AmortizedCosts[MeterSubCategory] ),
        ISINSCOPE ( Fct_EA_AmortizedCosts[MeterCategory] ),
            ALLSELECTED ( Fct_EA_AmortizedCosts[MeterCategory] ),
        -- default (in case neither column is on the visual)
        VALUES ( Fct_EA_AmortizedCosts[MeterCategory] )
    )
RETURN
IF (
    NOT ISEMPTY ( CatTable ),
    RANKX ( CatTable, [Total Amortized Cost], , DESC, DENSE ),
    BLANK ()
)

but it is not working

and to switch color:

Color by Rank :=
VAR r = [Rank (Current Axis)]
RETURN
SWITCH (
    TRUE(),
    ISBLANK ( r ), "#B3B3B3",
    r > 5,          "#B3B3B3",
    r = 1,          "#E81123",
    r = 2,          "#107C10",
    r = 3,          "#0078D4",
    r = 4,          "#FFB900",
    r = 5,          "#8E8CD8"
)

Can anybody show me how to implement model properly? 

Link to the model here:
https://drive.google.com/file/d/14lpEWK-MQ6JMU5_15WVh8Gv22AiGvdpR/view?usp=sharing

Best,
Jacek

[OwenAuger]

Hi @jaryszek 

Visual calculations are a convenient way of handling this.

See this article for example:
https://www.sqlbi.com/articles/using-visual-calculations-for-conditional-formatting/

The nice thing about visual calculations is that the same calculation works regardless of the field on the vertical axis.

In your case, I would

1. Add this visual calculation for both bar charts:

Color by Rank =
VAR r =
    RANK ( DENSE, ORDERBY ( [Sum of CostInBillingCurrency], DESC ) )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( r ), "#B3B3B3",
        r > 5, "#B3B3B3",
        r = 1, "#E81123",
        r = 2, "#107C10",
        r = 3, "#0078D4",
        r = 4, "#FFB900",
        r = 5, "#8E8CD8"
    )

2. Hide the visual calculation

3. Format > Properties > Data format > Set Color by Rank's Data Type to Text

4. Bars > Color > Conditional Formatting > Field value based on  Color by Rank

Does this work for you?

The problem with the Rank (Current Axis) measure is that in DAX SWITCH and IF cannot return table values. The other reply doesn't appear to take this into account.

[jaryszek]

thank you, this seems very nice! 

I am thinking now about robustness. Because i will need to add this to every new created chart where i have top 5 rule...

And if color will change...I need to adjust once again everything.

Best,
Jacek

[v-ssriganesh]

Hi @jaryszek,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @OwenAuger for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[jaryszek]

Not yet, 

I am thinking if I can make solution with custom calculations for visuals which will be robust for all visuals in a report.

[GrowthNatives]

Hi @jaryszek ,
You can follow these steps to implement it
1. Model MeterCategory and MeterSubCategory in a proper dimension (unique keys) and use those on visuals instead of the fact columns.

2.Create Rank Measure 

DAX 
Rank (Current Axis) :=
VAR RankTable =
    SWITCH (
        TRUE(),
        ISINSCOPE ( Dim_Meter[Subcategory] ),
            ALLSELECTED ( Dim_Meter[Subcategory] ),
        ISINSCOPE ( Dim_Meter[Category] ),
            ALLSELECTED ( Dim_Meter[Category] ),
        -- Fallback when neither is on the visual
            ALLSELECTED ( Dim_Meter[Category] )
    )
RETURN
IF (
    ISEMPTY ( RankTable ),
    BLANK(),
    RANKX (
        RankTable,
        CALCULATE ( [Total Amortized Cost] ),   -- ensure row→filter context
        ,
        DESC,
        DENSE
    )
)

3. Create Color Measure

DAX 
Color by Rank :=
VAR r = [Rank (Current Axis)]
RETURN
SWITCH (
    TRUE(),
    ISBLANK ( r ) || r > 5, "#B3B3B3",
    r = 1, "#E81123",
    r = 2, "#107C10",
    r = 3, "#0078D4",
    r = 4, "#FFB900",
    r = 5, "#8E8CD8"
)

4. Fact columns on the axis → duplicates can break ranks. Prefer a dimension.
• Top N visual filter → still works because we rank on ALLSELECTED; the Top N is applied after ranking, but the color measure is evaluated per visible item.
• Totals → ISINSCOPE prevents totals from getting a misleading rank; they’ll render as grey.
• Row-level security → honored automatically because we keep ALLSELECTED.

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together!🚀 [Explore More]

[jaryszek]

Hello,

thanks, it is not working at all:

Best,
Jacek

[GrowthNatives · Accepted solution]

Hi @jaryszek ,
Fix: branch around the whole RANKX, not around the table variable

-- Works at Category and Subcategory levels, blanks elsewhere
Rank (Current Axis) :=
VAR Result =
    IF (
        ISINSCOPE ( Dim_Meter[MeterSubCategory] ),
            RANKX (
                ALLSELECTED ( Dim_Meter[MeterSubCategory] ),
                CALCULATE ( [Total Amortized Cost] ),
                ,
                DESC,
                DENSE
            ),
        IF (
            ISINSCOPE ( Dim_Meter[MeterCategory] ),
            RANKX (
                ALLSELECTED ( Dim_Meter[MeterCategory] ),
                CALCULATE ( [Total Amortized Cost] ),
                ,
                DESC,
                DENSE
            ),
            BLANK ()
        )
    )
RETURN Result

If you don’t have a Dim_Meter table, replace with your columns (but a proper dimension is recommended to avoid duplicate categories).