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

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

Reply
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 ExpensesCompany1Company2Company3
Compensation50%60%55%
T&E10%15%5%
M&A8%10%12%
1 ACCEPTED SOLUTION
dax
Community Support
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.

 

View solution in original post

3 REPLIES 3
parry2k
Super User
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/SDynamic MedianCompany1Company2Company3Company4Company5Company6Company7Company8
Gross Margin91%90%92%91%97%89%94%93%91%
Compensation Exp85%68%84%87%75%94%76%86%93%
Salaries & Wages58%50%60%61%55%62%54%57%58%
Bonus11%8%11%12%9%14%10%13%16%
Commissions7%5%7%7%6%9%6%8%10%
Payroll Taxes3%2%3%3%2%4%2%3%4%
Benefits4%3%4%4%3%5%4%5%

6%

 

Data:

 AttributeValue
Company1Gross Margin90%
Company1Compensation Exp68%
Company1Salaries & Wages50%
Company1Bonus8%
Company1Commissions5%
Company1Payroll Taxes2%
Company1Benefits3%
Company2Gross Margin92%
Company2Compensation Exp84%
Company2Salaries & Wages60%
Company2Bonus11%
Company2Commissions7%
Company2Payroll Taxes3%
Company2Benefits4%
Company3Gross Margin91%
Company3Compensation Exp87%
Company3Salaries & Wages61%
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
Community Support
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.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.