cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

Conditional Formatting Highest Sub-Total

I've got a matrix in place at the minute using sub-totals for 10 pieces of data. One value is 13, the other is 11 but this can vary based on user input via filters I have in place.

 

Is there a way of achieving this using Conditional Formatting? When I go into the options to do this and choose to look at totals only, I have to put in a custom minimum value and maximum value which isn't what I need.

 

Visual expectation below:

 

Capture.PNG

 

I've been looking through a Microsoft document online which has a limitation of:

 

  • Conditional formatting applies only to the values of Table or Matrix visuals, and doesn't apply to any subtotals, grand totals, or the Total row.

This makes me assume I'm unable to achieve this using Conditional Formatting, is there a way of doing this using a measure perhaps?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Conditional Formatting Highest Sub-Total

Hi @lxtrxi ,

 

Try to create the following formula:

 

Measure =
VAR temp_table =
    SUMMARIZE (
        ALLSELECTED ( Table[cat]; Table[cat2] );
        Table[cat];
        "@Total"; SUM ( Table[value] )
    )
RETURN
    IF ( MAXX ( temp_table; [@Total] ) = SUM ( Table[value] ); 1; 0 )

 

the use the condititonal formating to equal 0 and 1:

 

MFelix_0-1598277271504.png

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Conditional Formatting Highest Sub-Total

@lxtrxi , I think new version it can apply to sub-totals too

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-april-2020-feature-summary/#_Conditional_f...

 

 

Background_sutotal.png



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User III
Super User III

Re: Conditional Formatting Highest Sub-Total

Hi @lxtrxi ,

 

What is the version of PBI that you are currently using?

 

If i'm not mistaken starting in May there is the possibility to have conditional formatting on the totals and sub totals.

 

 

conditional formatting.png

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Resolver I
Resolver I

Re: Conditional Formatting Highest Sub-Total

I can add the conditional formatting, the problem is that when I go into the options to do this and choose to look at totals only, I have to put in a custom minimum value and maximum value which isn't what I need.

 

It needs to be able to look at the values being shown and determine of those, which is the highest and highlight it as such.

 

Using August 2020 release.

Highlighted
Super User III
Super User III

Re: Conditional Formatting Highest Sub-Total

Hi @lxtrxi ,

 

Try to create the following formula:

 

Measure =
VAR temp_table =
    SUMMARIZE (
        ALLSELECTED ( Table[cat]; Table[cat2] );
        Table[cat];
        "@Total"; SUM ( Table[value] )
    )
RETURN
    IF ( MAXX ( temp_table; [@Total] ) = SUM ( Table[value] ); 1; 0 )

 

the use the condititonal formating to equal 0 and 1:

 

MFelix_0-1598277271504.png

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors