cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Can you use Subtotal for conditional formatting? X is higher/lower than the Avg - green, etc.

Hi folks,

Is there any way to use the Total/Subtotal columns for conditional formatting in a matrix? I'm not looking to format the total column, I'm looking to leverage it FOR my conditional formatting... for example, if X% is higher/lower than the average, highlight it red/green.

I'm using this to showcase where companies are spending more or less than the average/median in various categories. Sample data is included below, which we toggle on "Column Subtotals" and change it to average/median. That is the number I want to compare against.

 P&L Expenses Company1 Company2 Company3 Compensation 50% 60% 55% T&E 10% 15% 5% M&A 8% 10% 12%
1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

You could create below measure in Matrix, and create conditional formating measure for highlight

``````Measure = if(HASONEVALUE('Table'[Attribute]),SUM('Table'[Value]),CALCULATE(MEDIAN('Table'[Value]), ALLSELECTED('Table'[Attribute])))

Measure 2 = if(SUM('Table'[Value])>CALCULATE(MEDIAN('Table'[Value]), ALLSELECTED('Table'[Attribute])), "#abf5bf","#f7faa7")``````

You could refer to my sample for details.

Best Regards,
Zoe Zhi

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

3 REPLIES 3
Super User

@Anonymous yes it is possible, these % are measures in your model? How does your data looks like?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

So I've simplified it quite a bit here by removing the excess data for slicers/identifiers and other data for sorting, but this is the how our data is presented in our matrix and below that is how our existing data table is imported. We're actually leveraging a measure we created to calculate the median as opposed to using the "Subtotal" calc because we want the median to sit in FRONT of all the company data as opposed to the end, however in order to do so we're stacking two different Matrix tables on top of one another, otherwise we're unable to display it correctly.

Matrix:

 Natural I/S Dynamic Median Company1 Company2 Company3 Company4 Company5 Company6 Company7 Company8 Gross Margin 91% 90% 92% 91% 97% 89% 94% 93% 91% Compensation Exp 85% 68% 84% 87% 75% 94% 76% 86% 93% Salaries & Wages 58% 50% 60% 61% 55% 62% 54% 57% 58% Bonus 11% 8% 11% 12% 9% 14% 10% 13% 16% Commissions 7% 5% 7% 7% 6% 9% 6% 8% 10% Payroll Taxes 3% 2% 3% 3% 2% 4% 2% 3% 4% Benefits 4% 3% 4% 4% 3% 5% 4% 5% 6%

Data:

 Attribute Value Company1 Gross Margin 90% Company1 Compensation Exp 68% Company1 Salaries & Wages 50% Company1 Bonus 8% Company1 Commissions 5% Company1 Payroll Taxes 2% Company1 Benefits 3% Company2 Gross Margin 92% Company2 Compensation Exp 84% Company2 Salaries & Wages 60% Company2 Bonus 11% Company2 Commissions 7% Company2 Payroll Taxes 3% Company2 Benefits 4% Company3 Gross Margin 91% Company3 Compensation Exp 87% Company3 Salaries & Wages 61% etc…

So effectively, what we want to do...

1st, calculate a dynamic median in BI based on whatever companies are selected (could be any combination of companies based on the users choice). We only want tthe median for the selected companies, which we've managed to do in the way I mentioned above, which that formula looks like this:

Dynamic Median = if(not(ISFILTERED('Natural Expense Company Data'[Company])), "", calculate(median('Natural Expense Company Data'[Value]), allselected('Natural Expense Company Data'[Company])))

2nd - we want to highlight cells that are higher/lower than the calculated, dynamic median, either leveraging our existing measure we created or by using the subtotals that BI can calculate automatic (same result).

So I want to set up a rule that says if Company1's % of compensation expense is higher than the median for ALL companies, highlight it red, etc.

Community Support

Hi @Anonymous ,

You could create below measure in Matrix, and create conditional formating measure for highlight

``````Measure = if(HASONEVALUE('Table'[Attribute]),SUM('Table'[Value]),CALCULATE(MEDIAN('Table'[Value]), ALLSELECTED('Table'[Attribute])))

Measure 2 = if(SUM('Table'[Value])>CALCULATE(MEDIAN('Table'[Value]), ALLSELECTED('Table'[Attribute])), "#abf5bf","#f7faa7")``````

You could refer to my sample for details.

Best Regards,
Zoe Zhi

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors