Forum Discussion

VEG_Admin's avatar
VEG_Admin
Frequent Visitor
7 years ago
Solved

Conditional formatting multiple columns in matrix separately

I have a matrix with multiple columns, and I'd like the conditional formatting to apply to each column separately, instead of to the entire matrix.  The current result I have formats the background r...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, VEG_Admin 

    Sorry for my careless, you could try this formula for [$/SF/CostCode]

    $/SF/CostCode = RANKX(ALLSELECTED('Buildings'[Building Name]),[$/SF],,ASC)

    Result:

     

    By the way, in the 4th column, these three 0.01 values are different value indeed, please put four decimal places for [

    $/SF

    ].

     

     

    Best Regards,

    Lin

VEG_Admin's avatar
VEG_Admin
Frequent Visitor
7 years ago

Tom,

 

Thanks for the quick reply.  Sorry my previous post wasn't clear.  Here is the result I'm getting:

 

This is a matrix where Building is the Rows field, Cost code (1,2,3,4) is the Columns field, and $/SF is the Values field.  I am trying to see where there is a delta between buildings for each cost code, however, the large values in cost code 4 are skewing the entire matrix. so you don't see that there is a "relatively" large delta in cost code 1.  I'd like cost code 1 to look like this:

But to be included in the matrix as a whole, so that each column is formatted based on the range of values in that column, not the range of values in the entire matrix.

 

We don't have a non-secure share option, and I don't see a way to load the file on this site, but here is my sample data:

Thanks for the assistance.  It is really appreciated.

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

HI, VEG_Admin 

Just try this way as below:

Step1:

Create a measure

Measure for formatting = CALCULATE(SUM('Table'[$/SF]),ALLSELECTED('Table'[Cost Code]))

Step2:

When creating Conditional formatting, choose this measure for "Based on field"

Result:

 

Best Regards,

Lin

  • VEG_Admin's avatar
    VEG_Admin
    Frequent Visitor
    7 years ago

    Lin,

     

    Thank you for the reply.  That didn't do it, I'm thinking its becuase I've oversimplified my model.  There are actually three tables involved, one with the Buildings, one with the chart of accounts, and one with the transactions (GL).  When I tried your method below I get the same result now for every cost code across each building (i.e. it is calculating the total $/sf instead of the $/sf/cost code).  I recreated the model on my personal machine so that I can share it out.  The link is below.  I've left several visuals on there showing the different methods I tried.

     

    https://salsallc-my.sharepoint.com/:u:/g/personal/andy_valleyequitygroup_com/Ec_wpTghJm1Im4S_nM4cmfYBx8-SwHlczKMdcfmfS8aaQQ?e=orVMBy

     

    Thanks again for all the help.

     

    Andy

    • v-lili6-msft's avatar
      v-lili6-msft
      Community Support
      7 years ago

      hi, VEG_Admin 

      I couldn't download the sample pbix file, please re-upload.

       

      Best Regards,

      Lin

    • v-lili6-msft's avatar
      v-lili6-msft
      Community Support
      7 years ago

      hi, VEG_Admin 

      You just need to drag [$/SF] measure into Values of matrix but then do conditional formatting based on [$/SF/CostCode] measure

      For example:

      here is my pbix file, please try it.

       

      [$/SF/CostCode] is only used when do conditional formatting, don't drag it into Values.

       

       

      Best Regards,

      Lin

      • VEG_Admin's avatar
        VEG_Admin
        Frequent Visitor
        7 years ago

        This isn't quite right, the colors are carrying across all the columns for each building, i.e. Building 1200 is always red, regardless of whether it is the most or least expensive per square foot.  I need the red value to be the lowest amount in each column, not just the building with the lowest total.  So 1200 would be red in column B because it is the lowest, but green in column A becuase it is the highest.