How To Set Up Colors And Transparency Based On A Measure

If you ever used the default treemap visual in Power BI, you probably already know that it has a bunch of wild colors – depending on the theme you are using.

And it is very similar to the pie chart and donut chart coloring rules.

But if you want to create some advanced coloring setup your options are fairly limited…

How Can You Fix It?

Well, that’s exactly what I asked myself. I wanted to add a treemap visual to my report page but I wanted to drive colors based on the “group” each element belongs to.

In addition to that, I also wanted to adjust transparency based on the rankings within the group.

I had to realise almost immediately that creating these rules within the default formula-driven setup is not an option. Which led me to play with DAX until my problem was solved.


DAX Code

Allow me to share with you the DAX code I used to create a fully flexible group- and rank-driven coloring and transparency template:

Code =
VAR RankProd =
    RANKX ( ALL ( ‘Table'[Product] ), [Sum_Sales],, DESC )
VAR NoOfProduct =
    CALCULATE (
        DISTINCTCOUNT ( ‘Table'[Product] ),
        REMOVEFILTERS ( ‘Table'[Product] )
    )
VAR MinTransp = 20
VAR Transp =
    SWITCH (
        TRUE (),
        RankProd = 11,
        (
            100
                – DIVIDE ( 100 – MinTranspNoOfProduct – 1 ) * ( RankProd – 1 )
        ) / 100
    )
VAR Color =
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( ‘Table'[Brand] ) = “Brand 1”,
            “hsla(120, 100%, 25%, “ & Transp & “)”,
        SELECTEDVALUE ( ‘Table'[Brand] ) = “Brand 2”,
            “hsla(240, 100%, 25%, “ & Transp & “)”,
        SELECTEDVALUE ( ‘Table'[Brand] ) = “Brand 3”,
            “hsla(0, 100%, 50%, “ & Transp & “)”,
        SELECTEDVALUE ( ‘Table'[Brand] ) = “Brand 4”,
            “hsla(300, 100%, 50%, “ & Transp & “)”
    )
RETURN
    Color

If you want to know how I ended up using this code make sure to watch the video below where I share a step-by-step guide with you.

Roland

🙏Thanks for watching and if you liked this video please hit the 👍 button.

📢 Also be sure to subscribe to the channel so you never miss a new video.

Categories: Tips&Tricks

0 Comments

Leave a Reply

Your email address will not be published.