Help! Heat map using Matrix visual

[DanduMani104]

I have a Matrix visual that shows percentage % in Qtuarterly and monthly in Excel as bolow and I have applied gradient colounr in Excel like below.

this pictures indicates that after completion of Quarter it should show Gray colour in coloumn and remaining coloums the gradient colour should be applied. 

like same for months also.

I have used the below DAX measure to set a Field Value in Conditional Formating. 

BackgroundColor =

VAR CurrentMonthNumber = MONTH(TODAY())

VAR SelectedMonthNumber = MAX('M_DATE_D'[Ter Month])

VAR Percentage = 'S_SPL_QMR_DO_WEEKLY_2024_F'[Offer COS/24]  

VAR MinPercentage = 0 -- Define your min value for gradient calculation

VAR MaxPercentage = 1 -- Define your max value for gradient calculation

VAR ColorScale =

    IF(

        Percentage <= MinPercentage,

        "#FA8B8B",

        IF(

            Percentage >= MaxPercentage,

            "#48BC66",

            // Calculate the gradient color between "#FA8B8B" and "#48BC66"

            "#48BC66" -- Default to green if no specific color gradient is required

        )

    )

RETURN

IF(

    SelectedMonthNumber = 1,

    "#CCCCCC",

    IF(

        AND(SelectedMonthNumber = 0, Percentage = 0),

        "#FA8B8B",

        IF(

            SelectedMonthNumber = 0 && Percentage > 0 && Percentage < 0.75,

            "#FDF09F",

            IF(

                SelectedMonthNumber < CurrentMonthNumber,

                ColorScale,

                "#48BC66"

            )

        )

    )

)

 

 

But It gives like this

 

[DanduMani104]

I used below measure and it is worked

BackgroundColor =

---- VAR CurrentMonthNumber = MONTH(TODAY())

 VAR SelectedMonthNumber = MAX('M_DATE_D'[Ter Month])

 VAR Percentage = 'S_SPL_QMR_DO_WEEKLY_2024_F'[Offer COS/24]  

  RETURN

   IF(    SelectedMonthNumber=1 ,  

     "#CCCCCC",

      IF(AND(SelectedMonthNumber=0,Percentage=0), "#FA8B8B" ,  

            IF(SelectedMonthNumber=0 && Percentage>0 && Percentage<0.061,"#F9AFB2","#FDF09F"            

             )      

               )),

But it should take min and max percentages and should  apply gradient

[Anonymous]

Hi @DanduMani104 ,

When using field conditional formatting, the gradient does not take effect directly, you have to refine the DAX gradient yourself.

Best regards,
Community Support Team_ Scott Chang

[DanduMani104]

Hi, Soory for asking..

I can't understaand, Could you please explain how can we do that

[Anonymous]

Hi @DanduMani104 ,

This would be more complicated, for example, if the whole was 100, 0-10 would be one color, 10-20 would be another, and so on. 

Apply conditional table formatting in Power BI - Power BI | Microsoft Learn

Best regards,
Community Support Team_ Scott Chang

[DanduMani104]

How can I convert above DAX measure using SWITCH function.

[DanduMani104]

How can I convert above DAX measure using SWITCH function.

[Anonymous]

Hi @DanduMani104 ,

Try changing the IF statement to a SWITCH statement, as the IF statement may have dependencies that cause conditions to not match exactly.

SWITCH function (DAX) - DAX | Microsoft Learn

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.