Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Zeshansaif
Frequent Visitor

Need Help coloring Highest and lowest values in charts.

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.



Zeshansaif_0-1721373664587.png

 



2 ACCEPTED SOLUTIONS

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.

Ritaf1983_0-1721727074374.png

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

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

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 :
Ritaf1983_0-1721759577018.png

The updated pbix is attached

 

 
Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

7 REPLIES 7
Ritaf1983
Super User
Super User

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 :

Ritaf1983_0-1721398494710.png

and this is a daily data:

Ritaf1983_1-1721398635229.png

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 :
Ritaf1983_2-1721398787794.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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 

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.

Ritaf1983_0-1721727074374.png

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

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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.

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 :
Ritaf1983_0-1721759577018.png

The updated pbix is attached

 

 
Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Thankyou so much, you are super!!!

Happy to help 🙂

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

August Carousel

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.