cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jayeckles Member
Member

Conditional formatting for non-summarized item

Data source is a live-connected SSAS Tabular Model.

I have a numeric field in a table, and I'd like to conditionally format the values for that field in that table.  I have two issues:

 

1. If I have the value set to not summarize, I can't access a conditional formatting menu.  

2. The field is a GPA field.  I only want to highlight values <=1.5.  I can't figure out how to do anything other than change colors along the entire spectrum of values for the field.

7 REPLIES 7
th3h0bb5 Regular Visitor
Regular Visitor

Re: Conditional formatting for non-summarized item

Can you attach a picture of the visualization you're wanting?

 

If you're doing something like a table, just break it down to the highest level of detail. That way, each GPA value will have it's own line. You don't need to click 'don't summarize' that point and can apply conditional formatting. In the example I pasted below, each Month has it's own row in the data, so I can conditionally format the purchase values.

Untitled picture.png

jayeckles Member
Member

Re: Conditional formatting for non-summarized item

Here's a screenshot.  trmgpa is the field in question.  As you can see, if it's set to "Don't summarize", there is no "Conditional Formatting" option.  If I choose any summarization, say Average for example, then the "Conditional Formatting" option becomes available.

 

 

pbiexample.png

th3h0bb5 Regular Visitor
Regular Visitor

Re: Conditional formatting for non-summarized item

Given this visual, I don't see why you would need to click 'Don't Summarize'. Set the [trmgpa] calculation to average or sum and then apply the conditional formatting. Is there a reason that didn't work for you?

jayeckles Member
Member

Re: Conditional formatting for non-summarized item

I have no desire to summarize on the field.  For one, I have records that have no value (null) for that field.  For another, none of the summarizations are particularly useful in this context.  I just want the value to be red if < 1.5.  

Highlighted
th3h0bb5 Regular Visitor
Regular Visitor

Re: Conditional formatting for non-summarized item

Gotcha. Provided that this is the lowest level of detail though, your aggregate will be identical to your value.

 

For example, take the top row of your table:

asdf.png

 

If this represents a single row in your data source (let's say this is Bill's row), then Bill's GPA is 3.16. 3.16 is also the average of Bill's GPA and the sum of his GPA (since there is no other data taken into the calculation except for Bill's on this row).

 

Which takes me back to my initial remark; if you are at the lowest level of detail (Bill gets his own row and is not grouped in with anyone else), then you don't need to click 'not summarized' and can use conditional formatting.

jayeckles Member
Member

Re: Conditional formatting for non-summarized item

As I said before:

   For one, I have records that have no value (null) for that field.

If I use a summarization, those records get dropped from the table.  Not OK.

 

Not to mention, "Do Not Summarize" is the default.  It's not like I'm starting with a summarization and actively turning it off...

 

 

th3h0bb5 Regular Visitor
Regular Visitor

Re: Conditional formatting for non-summarized item

Sorry, I should have addressed that as well. There is a kind of odd work-around that will let you display null values in a table.

 

  1. Create a bar graph and pull in your GPA and one other field (it doesn't really matter which.)
  2. Click the axis and select 'Show Items with no data'
  3. Convert that bar graph back to a table and add in the rest of your rows. This new table will now display null values as their own row.

Here's the table before this process (with madeup data):

 

before.png

 

And here's the table created using the process above:

 

after.png

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 75 members 2,687 guests
Please welcome our newest community members: