Need Help coloring Highest and lowest values in charts.

[Zeshansaif]

I am trying to color the highest and lowest values by revenue in a chart. I've created measures for max values, min values and then measure for color. 

Individually max and min values are working correctly, however when I use them in a color measure, they are giving wrong results. 

Measure for Revenue 

Total Revenue = SUMX(FactTable,

        RELATED(DrugLookup[UnitSalesPrice])*FactTable[UnitsSold])

Measure for Max 

Max Revenue by Day =

VAR _max = MAXX(

        SUMMARIZE(

            DateTable,

            DateTable[Day name],

            "TotalRevenue", [Total Revenue]

        ),

        [TotalRevenue])

RETURN

_max

Measure For Min

Min Revenue by Day =

VAR _Min =

    MINX(

        SUMMARIZE(

            DateTable,

            DateTable[Day name],

            "TotalRevenue", [Total Revenue]

        ),

        [TotalRevenue]

)

RETURN

_Min

Measure for Color

Weekday Color =

VAR _max = [Max Revenue by Day]

VAR _min = [Min Revenue by Day]

RETURN

SWITCH(

    TRUE(),

    [Total Revenue] = _max, "Green",

    [Total Revenue] = _min, "Red"

)

Ju

I am attaching Screenshot for results as well.

[Ritaf1983 · Accepted solution]

Hi @Zeshansaif 

While I can't fully explain the reason, I've noticed some peculiar behavior involving context filter sections when using a DAX-generated calendar. My recommendation is to either utilize a calendar table directly from the database or construct one using Power Query. Your formulas appear to be correct. Once I replaced your DAX calendar with my PQ-generated calendar, everything functioned flawlessly.

The updated pbix is attached 

More information about date table power query include script is here :

https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

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

[Ritaf1983]

Hi @Zeshansaif 
Since you haven't attached the data file, I'll try to demonstrate the logic using a simple example of my own.
I created basic model like :

and this is a daily data:

The basic measure is :

Act = sum('Table'[Actual])

The measure for the color coding is :

color_ =

if([Act]=

CALCULATE(maxx(VALUES(DimDay[Day]),[Act]),ALLSELECTED('DimDay'[Day])),"Green",

If([Act]=

CALCULATE(MINX(VALUES(DimDay[Day]),[Act]),ALLSELECTED('DimDay'[Day])),"Red",

"GREY"))

The result is :

The pbix is attached

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

[Zeshansaif]

Hi, Thanks for Response. 
I am sharing drive link of the file. Go to Calender Sheet, in the bar chart I want to highlight highest and lowest. for 2024 it's working but not for other years. I dont know whats the reason.

Thanks.

Power BI File 

[Ritaf1983 · Accepted solution]

Hi @Zeshansaif 

While I can't fully explain the reason, I've noticed some peculiar behavior involving context filter sections when using a DAX-generated calendar. My recommendation is to either utilize a calendar table directly from the database or construct one using Power Query. Your formulas appear to be correct. Once I replaced your DAX calendar with my PQ-generated calendar, everything functioned flawlessly.

The updated pbix is attached 

More information about date table power query include script is here :

https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

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

[Zeshansaif]

Hi, Thanks for response.
I found the reason, as soon as I sort the 'day name column' by column 'daynumber', this anomaly appears. 

If you find the solution, please let me know. Thanks


Even in your screenshot the sorting is out of order.

[Ritaf1983 · Accepted solution]

Hi @Zeshansaif 
Sorry, i didn't check the order of the days.
You can update the formula to :

Weekday Color =

if(

    CALCULATE(MAXX(

        SUMMARIZE(FactTable,DateTable[Day Name],DateTable[Day of Week],"total revenue" ,[Total Revenue]),[total revenue]),ALLSELECTED(DateTable[Day Name],DateTable[Day of Week]))=[Total Revenue],"Green",

        if(

    CALCULATE(MINX(

        SUMMARIZE(FactTable,DateTable[Day Name],DateTable[Day of Week],"total revenue" ,[Total Revenue]),[total revenue]),ALLSELECTED(DateTable))=[Total Revenue],"Red","blue")

It will work with a sorted day too :

The updated pbix is attached

[Zeshansaif]

Thankyou so much, you are super!!!

[Ritaf1983]

Happy to help 🙂