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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
jonnyA
Responsive Resident
Responsive Resident

8/17/21 | Create a formula to highlight 10% above and 10% below Group%

Create a formula to highlight 10% above and 10% below Group % or if there is a better way to do it, possibly conditional formatting, i dont know?

----------------------------------------------------------------

Below is my matrix.  Hoping someone could help me to figure out a formula that highlights the Provider % when it is 10% above or below the "Group %" ...

jonnyA_0-1629214050118.png

 

Here are some of the Fields and measures that I am using in my matrix

1. (Field) Provider Name

2. (Field) CPT

3. Visits "Count Distinct"

4. Provider % aka "CPT Code" shown as "Count" with value shown as Percent of row total.

5. (This is a calculated Measure) Group % aka "Group Average Ignore Degree".  For the first part of this measure you will need "Group Average" which is:  

Group Average = CALCULATE(
[Group CPT Count],
ALL(Dataset_Charge_Dataset[CPT_Code], Dataset_Charge_Dataset[Provider_Name]))
And here is the formula I use ... 
Group Average (Ignore Degree) = IF (
ISFILTERED (Dataset_Charge_Dataset[Provider_Degree]), CALCULATE([Group Average], REMOVEFILTERS(Dataset_Charge_Dataset[Provider_Degree])), [Group Average])
6. (This is a calculated Measure) National % = 
National Average = IF (
ISFILTERED (Dataset_Charge_Dataset[Provider_Name]), CALCULATE(SUM(MCRPysicianSupplierProcSummary[PSPS_Submitted_Service_Cnt]), REMOVEFILTERS(Dataset_Charge_Dataset[Provider_Name], Dataset_Charge_Dataset[Facility_Name])), SUM(MCRPysicianSupplierProcSummary[PSPS_Submitted_Service_Cnt]))

 

Hopefully the information I gave you is enough.  I cannt attach sample data, but if you send me your email I will send you over a sample report that someone sent me form the Power BI community where they got it to work.

 

I can send you this file if you drop me your email!

 

I think the reason I cannot get to work on my end is because on the sample data for the "Group%" Field they are using a Field that has the funny looking "E" in front of it.  Where my Group % is a calculated Measure.

jonnyA_0-1629220463487.png

 

 

2 ACCEPTED SOLUTIONS
DataZoe
Microsoft Employee
Microsoft Employee

@jonnyA You can bring your flag into your matrix as well to make sure it's calculating correctly.  I have attached a PBIX where you can see it applied.

 

DataZoe_0-1629299510312.pngDataZoe_1-1629299564780.png

 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

Icey
Community Support
Community Support

Hi @jonnyA ,

 

In addition, if you want to hide the flag measure in the matrix visual, try this:

 

hide column.gif

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
DataZoe
Microsoft Employee
Microsoft Employee

@jonnyA You can create another measure that flags if it is 10% +/- of the Group, like so:

 

10 Percent Flag = IF ( [Provider %] >= [Group %] + 0.1 ||  [Provider %] <= [Group %] - 0.1, 1, blank())

 

  1. Then in your Matrix you can go to the Matrix's Format, choose Conditional Formatting, and then in the drop down pick [Provider %]. 
  2. Turn on Background Color, then click "Advanced Controls". 
  3. From there you can switch the "Format By" to "Rules". 
  4. Change "Based on field" to your [10 Percent Flag]
  5. Then create a rule so that "If value is 1 then" and choose the color you want to highlight it.

DataZoe_0-1629248595735.png

 

Hope this helps!

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

jonnyA
Responsive Resident
Responsive Resident

I also tried your suggestion in conditional formatting ...

jonnyA_1-1629255730791.png

But that Conditional Formatting Highlighted every row (See screenshot below)

jonnyA_2-1629255894487.png

Do you have any Conditional Formatting suggestions?

 

DataZoe
Microsoft Employee
Microsoft Employee

@jonnyA You can bring your flag into your matrix as well to make sure it's calculating correctly.  I have attached a PBIX where you can see it applied.

 

DataZoe_0-1629299510312.pngDataZoe_1-1629299564780.png

 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Icey
Community Support
Community Support

Hi @jonnyA ,

 

In addition, if you want to hide the flag measure in the matrix visual, try this:

 

hide column.gif

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jonnyA
Responsive Resident
Responsive Resident

@Icey That's a great tip, thank you!

Icey
Community Support
Community Support

Hi @jonnyA ,

 

If @DataZoe  has solved your problem, please accept his reply as a solution.😉

 

 

Best Regards,

Icey

DataZoe
Microsoft Employee
Microsoft Employee

Thank you @Icey! That is a good way to hide them, and they can also be removed from the matrix once you have determined they are calculating correctly too.  

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

jonnyA
Responsive Resident
Responsive Resident

@DataZoe 

 

Thank you for the time you put in for your response.

 

I might be doing something incorrectly.

 

I created the formula you said to create.

 

Maybe you can help me with Conditional Formatting.

 

This is what I tried, but was not successful in my matrix being color coded.

 

jonnyA_0-1629255590387.png

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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