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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Peter_Verweij
Regular Visitor

matrix conditional formatting on % average of values based on average total

Hello everyone,

 

I've got a problem fixing conditional formating based the values based on de total

Searching for an answer on the community site i did not accomplish my goal.

My goal is to conditional format the values based on the total. Just like the example below.

 

Below the average = Red

Same as average = Orange

Above average = Green

 

Is this possible?

 

Kind regards, Peter

 

Knipsel.PNG

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Peter_Verweij ,

I updated your sample pbix file(see attachment), please check whether that is what you want.

1. Create a calculated column as below to get the percentage of payed vs invoiced per row

Percent of payed and invoice = DIVIDE('FactTable'[Payed],'FactTable'[Invoice],0)

yingyinr_0-1638500327358.png

2. Update the formula of measure [utilization] as below

utilization = AVERAGEX('FactTable','FactTable'[Percent of payed and invoice])

3. Update the formula of measure [Conditional formattingas below

yingyinr_2-1638500509639.png

Note: I found that the fact table contains the period of 201913 and 202013, but there is no such period in the calendar table, so I excluded the period of 201913 and 202013 to calculate the percentage...

yingyinr_3-1638500714145.png

Best Regards

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Hi @Peter_Verweij ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a measure as below to set the color base on different conditions

Conditional formatting = 
VAR _selcontractor =
    SELECTEDVALUE ( 'Table'[Contractor] )
VAR _invoice =
    AVERAGEX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Contractor] = _selcontractor ),
        [Invoice]
    )
VAR _payed =
    AVERAGEX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Contractor] = _selcontractor ),
        [Payed]
    )
VAR _avgofper =
    DIVIDE ( _payed, _invoice, 0 )
RETURN
    IF (
        [Percent of Invoice and Payed] < _avgofper,
        "Red",
        IF ( [Percent of Invoice and Payed] = _avgofper, "Orange", "Green" )
    )

2. Configure conditional formatting: set the backgroud color for the Values field: Percent just as below screenshot

yingyinr_0-1637311652662.png

Best Regards

Hello @Anonymous ,

 

Thank you for your help.

I've used your sample in my file, but i am experising some troubles with the correct % of  Conditional formatting measure.

 

i hope you can help me.

If i use the VAR _avgofper (the first matrix in the picture) with this in the return section for troubleshooting. 

Then i see that the average is higher than expected. What i am experiencing is that the _avgofper is not affected by the selections. When im searching the 79% of th efirst row in my excel file, i can not find it if i divide the payed and invoiced for the periodes 202101 till 202109.

 

The measure used in the coloured matrix is correct: Percent of Invoice and Payed

 

Peter_Verweij_0-1637577795462.png

 

Is there an option to put the year and the period in the allselected code?

 

Anonymous
Not applicable

Hi @Peter_Verweij ,

Could you please share your simple sample pbix file with me in order to make troubleshooting? Later I will check it and provide you a suitable solution. Thank you.

Best Regards

Hello @Anonymous ,

 

I've tried to make a simple file of the real one. it has 3 pages. i am looking for the same calculation method for these 3 pages. i've translated the pages into english.

 

What i cannot understand is the calculation of power bi versus excel on page: utilization per Product

 

i am looking for the excel % in the power bi calculation.

 

Also the percentages that are the same = are red or green.

probebly due to the decimals.

 

The file is here: https://www.dropbox.com/s/xhco6u5s8qft6os/Conditional%20formatting.pbix?dl=0

 

Thank you for helping me.

 

Kind regards, Peter

 

 

Anonymous
Not applicable

Hi @Peter_Verweij ,

I updated your sample pbix file(see attachment), please check whether that is what you want.

1. Create a calculated column as below to get the percentage of payed vs invoiced per row

Percent of payed and invoice = DIVIDE('FactTable'[Payed],'FactTable'[Invoice],0)

yingyinr_0-1638500327358.png

2. Update the formula of measure [utilization] as below

utilization = AVERAGEX('FactTable','FactTable'[Percent of payed and invoice])

3. Update the formula of measure [Conditional formattingas below

yingyinr_2-1638500509639.png

Note: I found that the fact table contains the period of 201913 and 202013, but there is no such period in the calendar table, so I excluded the period of 201913 and 202013 to calculate the percentage...

yingyinr_3-1638500714145.png

Best Regards

@Anonymous Thank you for your assistence!

 

selimovd
Super User
Super User

Hey @Peter_Verweij ,

 

yes, that's possible with a measure for the conditional formatting.

Create a measure that will compare the current value vs. the Average. When it's above, the color should be green, when equal orange and below red. Then you can use the measure as formatting option:
Conditional table formatting in Power BI Desktop - Power BI | Microsoft Docs

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.