Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Conditional Formating | Each Column - Separatly

Hello,

I guess this should be a standard requirement but I cant find any solutions to this.

 

The case is pretty simple. I have dates (column), hours (Row) and lets say conversion as a value. I want a conditional Formating for each day separatly.

 

When I am actually using the conditional formating it ditribute the colors basically on all the data. I want to know what is the best hours for every day (row) separatly and make it visible with colors.

 

I hope I was clear enough. Again I cant imagine there is no accessible workflow for this requierement (?).

 

Thank you for your time.

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , You need to create a measure like this and use that in conditional formatting "Field" option

     

    Colour = 
SWITCH(TRUE(),
Table[Date] < TODAY(), "red",
Table[Date] = TODAY(), "orange",
"green")

Color sales = if(AVERAGE(Sales[Sales Amount])<170,"green","red")
Color Year = if(FIRSTNONBLANK(Table[Year],2014) <=2016,"lightgreen",if(FIRSTNONBLANK(Table[Year],2014)>2018,"red","yellow"))

Color  = if(FIRSTNONBLANK(Table[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170 
,"lightgreen",if(FIRSTNONBLANK(Table[Year],2014)>2018,"red","yellow"))
Color sales = if([Sales Today] -[sales yesterday]>0,"green","red")

color =
switch ( true(),
FIRSTNONBLANK(Table[commodity],"NA") ="commodity1" && sum(Table[Value]) >500,"lightgreen",
FIRSTNONBLANK(Table[commodity],"NA") ="commodity2" && sum(Table[Value]) >1000,"lightgreen",
/// Add more conditions
"red"
)

     

    refer

    https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
    https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Here are some screenshot who should clear up what what I am looking for. I manipulated one value (the 27 at 16:00) to make my issue more clear.

     

    The left Screenshot is the conditional formating I am looking for.

     

     

    The left conditional formating is the result I am looking for but I have to define the values (max. and min.) in upfront.

    The screenshot on the right is the conditional formating with the dynamic parameter I am lookig for but the result is not the right one.

     

    • V-lianl-msft's avatar
      V-lianl-msft
      Community Support
      5 years ago

      Hi Anonymous ,

       

      Could you share the sample data?

      In my tests, I don't need to set the maximum and minimum values to get the expected result.

       

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        First, thank you for the answer. It is not working in your example neather. Look at the first row, there is no max or min in this row, its just over all values.

         

        As far as I see in your case PowerBI ist looking on all the data and distribute the color based on the highest and lowest color on all values. I want a color distibution based on each Colomun. This means a highest and lowest value on each column.

         

        In my example sample I put an atificial very high value in the first day to show that PowerBI is calculating every value together. If Power BI had calculated each column separatly we had dark and light colors on every column.

         

        And dont have the sample of data anymore but this is a problem I am often confronted with. Just put a very high value in one column and you should see the problem in your data.