Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Using a measure as a benchmark for table colouring

Hi,

 

I woud like to use a load time measure to benchmark my table numbers: 

  • if a number in the table is above the measure the background is red
  • if a number in the table below the measure the background is green

I used this formula for creating my measure: 

Avg Load Time Desktop = 
CALCULATE(
AVERAGE(
'Page load time'[Avg. Page Load Time (sec)]
), 
'Page load time'[Device Category]= "desktop"
)

here is how the table looks: 

 

The measure works perfectly, I just can't figure out how to set a measure as a benchmark in conditional formatting, any help is highly appreciated!

 

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi, Anonymous 

    Maybe you need to modify your measure as below:

    Avg Load Time Desktop2 = 
AVERAGEX (
    FILTER (
        ALL ( 'Page load time' ),
        'Page load time'[Device Category] = "desktop"
    ),
    'Page load time'[Page Load Time (sec)]
)

    Then create measure  "color" as below,then apply  it to backgroud color conditional formating.

    Color = IF(MAX('Page load time'[Page Load Time (sec)])>[Avg Load Time Desktop2],"Red","yellow green")

     

     

    You also  can create measure as below:

    Measure 2 = IF(MAX('Page load time'[Page Load Time (sec)])>[Avg Load Time Desktop2],1,0)

     

    For more details ,please check the attached pbix file and this related document.

     

     

    Best Regards,
    Community Support Team _ Eason

4 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

    Maybe you need to modify your measure as below:

    Avg Load Time Desktop2 = 
AVERAGEX (
    FILTER (
        ALL ( 'Page load time' ),
        'Page load time'[Device Category] = "desktop"
    ),
    'Page load time'[Page Load Time (sec)]
)

    Then create measure  "color" as below,then apply  it to backgroud color conditional formating.

    Color = IF(MAX('Page load time'[Page Load Time (sec)])>[Avg Load Time Desktop2],"Red","yellow green")

     

     

    You also  can create measure as below:

    Measure 2 = IF(MAX('Page load time'[Page Load Time (sec)])>[Avg Load Time Desktop2],1,0)

     

    For more details ,please check the attached pbix file and this related document.

     

     

    Best Regards,
    Community Support Team _ Eason

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Anonymous - Conditional formatting- > BackGround Color - > It will give you options how you want to set up the formatting.

     

    Aooreciate your kudos!! Mark this post as solution if this helps