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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
wvadik
Helper III
Helper III

Background color only for one subtotal in matrix

I have the matrix table with hierarhy in rows (I put three fields in rows). Matrix has 2 subtotals and one grand total.

I need to background colorize the first subtotal.

How to do it?

 

Many Thanks

13 REPLIES 13
wvadik
Helper III
Helper III

I guess what the problem might be. In the conditional formatting pane, you can only select fields and measures that are in text format, and in ssas-cube my measure has a general format. How to specify the text format for the measure in the tabular model (compatibilityLevel = 1400)?

Click on the measure and in the Properties pane you should be able to change the format to text

This is the problem, the option Text in the list is missing

 

wvadik_0-1607506242512.png

 

Not sure what the problem is...I have a similar color coding measure in an AAS data model.  Format is general and I have no trouble with it

littlemojopuppy_0-1607528259933.png

 

littlemojopuppy
Community Champion
Community Champion

Weird...I had no trouble with that at all.  Try removing the conditional formatting and reapplying?

I tried to create a new report in PowerBI that uses data from the same cube and in this case there was the same problem

littlemojopuppy
Community Champion
Community Champion

Hi!  Try this out...have to go through the hierarchy lowest level to highest.  The highest level is always in scope if a lower level is.  I substituted red for light green...find the hex code for the shade of green you want to use and put in the first six character positions for the hex code here.  The last two indicate the degree of transparency.

littlemojopuppy_0-1607360845859.png

 

Great!
MyExample.pbix works with
Color = SWITCH(
TRUE(),
ISINSCOPE(Dim3[name3]), BLANK(),
ISINSCOPE(Dim2[name2]), BLANK(),
ISINSCOPE(Dim1[name1]), "lightgreen",
BLANK()
)
But in real report (all data in Tabular ssas-cube) I can't to select a measure "Color" in the Conditional Formatting pane. This measure is displayed in the list, but it is not selectable. Why?

littlemojopuppy
Community Champion
Community Champion

Would need to see a screen shot or if you can share the PBIX

Here is my example.  I need background only for Name1 level.
In reality, the data is in the tabular ssas model. In my main report I'm trying to select a measure "Color" in Сonditional Formatting pane to set the background color for subtotals, then this measure is displayed in the list, but it is not selectable.

wvadik
Helper III
Helper III

Thanks.
In my case: I have 4 tables:
TableFact (id_dim1, id_dim2. id_dim3, count)
Dim1 (id, name) - 1st level in matrix
Dim2 (id, name) - 2nd level in matrix
Dim3 (id, name) - 3rd level in matrix

TableFact had one measure (Cnt := sum(count)) and I added another new measure ( Color := IF(ISINSCOPE(Dim1[name]), "lightgreen", BLANK()))
I'm trying to select a new measure in Сonditional Аormatting to set the background color for subtotals, then this measure is displayed in the list, but it is not selectable
what am I doing wrong?

littlemojopuppy
Community Champion
Community Champion

You can create a measure using ISINSCOPE to test for a given level of the hierarchy.  If true, assign a hex color code.  In the properties use that measure in conditional formatting.  Hope this helps.

amitchandak
Super User
Super User

@wvadik , if the first subtotal means one-row item.

You have the option to color and subtotal .

Try a color measure like

color = if(isinscope(Table[row]), "red","white")

use that in field formatting

refer https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors