Forum Discussion

ansa_naz's avatar
ansa_naz
Icon for Continued Contributor rankContinued Contributor
7 years ago
Solved

Apply conditonal formatting to rows in a table visual without summarisation

Hi all

 

Is there any way to apply conditional formatting to individual values in a table visual, and not using summarisation?

 

See below example data:

 

Invoice No           Site Name              Amount        Days Overdue

0232                   Site A                     1222.35        35

0233                   Site B                      100.25         13

0233                  Site C                       52.35           13

0233                  Site B                       13.00           13

5233                  Site C                       19.52           96

 

 

I want to highlight the background red in column 'Days Overdue' wherever Days Overdue is greater than 60. If I try to set up Conditional Formatting on this column, I am asked to select a summarisation field, which I dont need as I want to do the formatting just based on the value in that row. See below where I am asked to add a summary field:

 

 

I thought maybe I could set the based on field to Invoice No, and then do a Sum summarisation. However, this does not work as selecting Invoice No does not give me the option to select Sum as the Summarisation, only Count:

 

 

Is there any way to do this based on row values, without any summarisation?

 

Edit - One invoice No in the dataset can have multiple lines, example above has been amended to take this into account

 

Cheers for all help

Need Help
  • TomMartens's avatar
    TomMartens
    7 years ago

    Hey,

     

    I assume that you use a Power BI Desktop version that supports to add conditional field formatting by using a field value meaning a measure.

     

    I created this measue:

    apply red due to Days Overdue = 
IF(HASONEVALUE(Table1[Invoice No])
    ,IF(
        CALCULATE(SUM('Table1'[Days Overdue])) >= 60
        ,"Red"
        ,BLANK()
    )
    ,BLANK()
)

    After this you can add a conditional formatting "Background color" to the column "Days Overdue" like so:

     

    and then this:

     

    This leads to this:

     

    Hopefully this is what you are looking for!

     

    Regards,

    Tom

6 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    7 years ago

    Hey,

     

    I assume that you use a Power BI Desktop version that supports to add conditional field formatting by using a field value meaning a measure.

     

    I created this measue:

    apply red due to Days Overdue = 
IF(HASONEVALUE(Table1[Invoice No])
    ,IF(
        CALCULATE(SUM('Table1'[Days Overdue])) >= 60
        ,"Red"
        ,BLANK()
    )
    ,BLANK()
)

    After this you can add a conditional formatting "Background color" to the column "Days Overdue" like so:

     

    and then this:

     

    This leads to this:

     

    Hopefully this is what you are looking for!

     

    Regards,

    Tom

    • ansa_naz's avatar
      ansa_naz
      Icon for Continued Contributor rankContinued Contributor
      7 years ago

      Thanks TomMartens thats perfect! Didnt think I would need to create a measure for this, in my mind it should just work by looking at each row value

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi ansa_naz,

     

    The "sum" won't be a problem in your scenario. Because the [Days Overdue] will always be a single value. For instance,

    Invoice No           Site Name              Amount        Days Overdue

    0232                   Site A                     1222.35        35

    0233                   Site B                      100.25          13

    5233                   Site C                       19.52          96

    5255                   Site A                       30               50

    If the result is as follows, the conditional formatting can't work without "sum". The result isn't the one you want either.

    Site Name         Days Overdue

    Site A                85

    Site B                13

    Site C                96

     

    Best Regards,
    Dale

    • ansa_naz's avatar
      ansa_naz
      Icon for Continued Contributor rankContinued Contributor
      7 years ago

      Thanks for the reply v-jiascu-msft. Dont quite understand if you are suggesting a solution here or not, but using Sum didnt get me the correct answer as the Conditional Formatting doesnt give an option of Sum when selecting the Invoice No column as the 'Based on Field' selection

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi ansa_naz,

         

        It's my pleasure. It seems working in my test. Please refer to the snapshot below. Does the [Days overdue] column have a numeric type?

        Apply-conditonal-formatting-to

         

        Best Regards,
        Dale