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

[Peter_Verweij]

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

[v-yiruan-msft]

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

Best Regards

[Peter_Verweij]

Hello @v-yiruan-msft ,

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

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

[v-yiruan-msft]

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

[Peter_Verweij]

Hello @v-yiruan-msft ,

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

[v-yiruan-msft · Accepted solution]

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)

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 formatting] as below

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...

Best Regards

[Peter_Verweij]

@v-yiruan-msft Thank you for your assistence!

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic