Conditional Formatting where Provider % is 10% above or 10% below Group average

[jonnyA]

Hey everyone,

I'm trying to use Conditional Formatting that will highlight the #'s where the "Provider %" is 10% below or above the "Group Average".  See my screenshot as an example: The yellow highlighted #'s are all at least 10% higher than that of the Group %.  And the Green Highlighted #'s fall at least 10% below the Group %.  Thanks in advance for the help!

[jonnyA]

@amitchandak , sorry to bother you again, but any chance you could help me finish the measure in question?  I responded earlier this afternoon, but didnt hear back. I have a lot of red squigglies.  I think one issue might be that where I have entered the CPT Code (Highlighted yellow) aka Provider %, the measure is looking for a calculated measure and "CPT Code" is not a calculated measure.  I dont know?  Any help is appreciated, thank you!  

[HashamNiaz]

Hi !

Please answer following question. 

[Provider %] & [Group %]  are measures. Seems like they are not from your sample images. Then what aggregation you are using for those while plotting them in matrix visual.

Please specify source calculation for both these 2 KPIs

Regards,

Hasham

[jonnyA]

I think i was incorrect in my original question ... Here is what I am looking to do now.

I'm looking for some help on creating a measure that highlights the %'s in column "%RT Count of CPT_Code" which is 10% above and 10 below the total avgerage for "%RT Count of CPT_Code".

The %'s highlighted in yellow represent any % that is higher than 25.50% which is 10% above the 15.50% Total.

The %'s highlighted in green represent any % that is lower than 5.50% which is 10% below the 15.50 Total.

[v-kelly-msft]

Hi  @jonnyA ,

Create a measure as below:

Measure 2 = IF(MAX('Table'[%RT])<'Table'[Measure]*0.9,1,
IF(MAX('Table'[%RT])>'Table'[Measure]*1.1,2,BLANK()))

Then set a conditional formatting for background as below:

Finally you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[jonnyA]

Thank you soooo much for your response.  I feel like this formula will work, however, I am having an issue with the 18.04% part of the formula.

So, my "Group %" is a measure I created.  So I have the calculator icon in from of my measure.  Here is the formula i am using for Group % aka Group Average (Ignore Degree) ... 

Group Average (Ignore Degree) = IF (

ISFILTERED (Table[Provider_Degree]), CALCULATE([Group Average], REMOVEFILTERS(Table[Provider_Degree])), [Group Average])

You are using a measure that has the funny looking E icon for Group %.

My issue is when I try to create this measure ... 

Measure = sum('Table'[Group%]) ... I cannot with my dataset because my Group % is not coming to add in my calculated measure.

Have any suggestions on what I can do?

[v-kelly-msft · Accepted solution]

Hi @jonnyA ,

I dont know your measure of Group before,so I tested with a simple aggregation measure which can be aggregated.

For your measure,it is a judgement statement,you could use below dax expression instead:

Measure= Sumx(Values('Table'[Provider_Degree]),'Table'[Group%])

If you still cant work out,could you pls provide some sample data or your .pbix file for test?

Remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[HashamNiaz]

Hi !

Try creating a measure to calculate Differenc between Provider % & Group %. Then use this measure in Conditinal Formatting & use Rules to highlight If Differece is >= .1 Then highlight "Yellow", Add another rule if Difference is <=.1 Then highlight "Green"

Regards,

Hasham

[amitchandak]

@jonnyA , Try a color measure like

New measure =
var _1 =[Provider %] - calculate([Provider %], allselected(Table))
Switch( True() ,
_1 <=.1 ||( _1 <0 && _1 >=-.1), "green",
"yellow"
)

You can use conditional formatting with field value option.

[jonnyA]

Thank you, but I need some help writing the measure with the data I have, let me try to explain ...  Can you help me fill in the gaps with the measure you gave me?!?!

Visits, aka "Voucher #" is Count Distinct

Provider %, aka "CPT Code" is Count

Group %, aka "Group Average Ignore Degree is shown as % of Grand total

National Average is % of Grand total