Matrix table with dynamic gradient background color when there's hierarchy level

[LucasBW]

Good day everyone.

I'm struggling with a obstacle in Matrix table about having gradient color in a column, when I have hierarchy level row in the table.
When I go to Cell Elements > Background and select Gradient, it only let me have dynamic minimum and maximum color when it's selected "Values Only" in the field "Apply to". But I need the format to my totals as well, and it do not let me have a dynamic value, only static value (image below)

And if I choose Values Only, it allows me to set gradient values and color, but only applyes to the second level, not for the first one (image below) 

Can this be fixed or have a walkaround?

[v-sgandrathi]

Hi @LucasBW,

May we know if the support team was able to resolve your issue?

If a solution was provided, please consider sharing it with the community to help members facing similar challenges.

Thank you for your participation and cooperation.

[v-sgandrathi]

Hi @LucasBW,

As this appears to be a product limitation rather than a configuration issue, you might want to submit a Microsoft Power BI / Fabric support ticket. They can officially confirm the limitation and let you know if there are any planned enhancements or roadmap updates for dynamic gradient conditional formatting in Matrix visuals with hierarchy levels and totals. Including the existing idea request in your ticket could help boost visibility.
Create a Fabric and Power BI Support Ticket - Power BI | Microsoft Learn

Thank you.

[v-sgandrathi]

Hi @LucasBW,

Thank you @cengizhanarslan @danextian for your response to the query.

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[LucasBW]

Thank you @cengizhanarslan @danextian for your effort in trying to help. I actually had found this walkaround but unfortunately the colors are not so dynamic as the automatic way in PBI. Its unfortunate that a simple format is not available for us still.
But thanks again for your help!

[cengizhanarslan]

Instead of the built-in Gradient option, drive background color with a measure. For example:

BG Color =
VAR v = [Deposit-Withdrawal]
VAR MinV = CALCULATE ( MIN ( [Deposit-Withdrawal] ), ALLSELECTED ( 'Dim'[Level] ) )
VAR MaxV = CALCULATE ( MAX ( [Deposit-Withdrawal] ), ALLSELECTED ( 'Dim'[Level] ) )
VAR Ratio = DIVIDE ( v - MinV, MaxV - MinV )
RETURN
SWITCH (
    TRUE(),
    Ratio <= 0.5, "#66CC66",
    "#FF9999"
)

Then:

• Cell Elements → Background color

• Format by: Field value

• Based on: BG Color

By the way what @danextian is a way more better solution then this as it is more dynamic at the end. But if you want to keep it simple you can stick with this as well with a bit change in the measure depending on your needs.

[danextian]

Hi @LucasBW 

That is a known limitation. Gradient conditional formatting on totals require values to be hardcoded. This has already been posted as an idea https://community.fabric.microsoft.com/t5/Fabric-Ideas/Conditional-Formatting-should-apply-format-in...

A workaround is to use a measure that returns a color dynamically. This is a bit advance but this is a good read

 https://www.esbrina-ba.com/conditional-formatting-with-a-dynamic-divergent-color-gradient-for-value...