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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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