Forum Discussion

jaryszek's avatar
jaryszek
Super User
10 months ago
Solved

Create colors based on value, not category

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...
Need Help
Show and Tell
  • GrowthNatives's avatar
    GrowthNatives
    10 months ago

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


OwenAuger's avatar
OwenAuger
Super User
10 months ago

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's avatar
    jaryszek
    Super User
    10 months ago

    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's avatar
      v-ssriganesh
      Community Support
      10 months ago

      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's avatar
        jaryszek
        Super User
        10 months ago

        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.