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

[Anonymous]

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%

[parry2k]

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

[Anonymous]

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.

[dax · Accepted solution]

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.