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

Highlighted
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.  

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
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 312 members 3,171 guests
Please welcome our newest community members: