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


jaryszek's avatar
jaryszek
Super User
10 months ago

Hello,

thanks, it is not working at all:

 

 

Best,
Jacek

 

GrowthNatives's avatar
GrowthNatives
Super User
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).