Background color only for one subtotal in matrix

[wvadik]

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

[wvadik]

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)?

[littlemojopuppy]

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

[wvadik]

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

[littlemojopuppy]

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]

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

[wvadik]

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]

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.

[wvadik]

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]

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

[wvadik]

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]

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]

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]

@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