( DAX issues) in Converting excel report to Matrix visual

[nandanw]

Hi All, 

I'm converting excel based report to Matrix visual, my matrix looks like this... 

ROWS : Tier1, tier2,tier3

Columns: main section, business, teams, leader, sub section.

VALUES : its single measure combined 5 measures of ( target, actual, total target, total actual, notes)

target, actual will show under each leader(under main section) , total target, total actual, notes will show at end of matrix ( under sub section, no break up under sub section these 3 cols will show as 3 columns no matter how many leaders, teams, business ) for now I got view I wanted,

• but I want custom alignment ex: notes left, rest center.
• And I want to apply condition formatting only on actual based on target but here it's applying for both cells. 
• Since it's single measure I'm unable to do such changes. I tried splitting measure but each measure value shows under each leader. 

I have used isinscope for total target, total actual, notes to hide it's from business, teams, leader. 

Long story short is there any way I can do that changes alignment & condition formatting, even if it's splitting 1 measure to 5 measures. 

[v-venuppu]

Hi @nandanw ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @nandanw ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Shahid12523 @MFelix for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[Shahid12523]

Matrix visuals in Power BI don’t allow per-column alignment or selective conditional formatting if all values come from a single combined measure.

To control alignment & formatting:

1. Split your combined measure into separate measures (Target, Actual, Total Target, Total Actual, Notes).
2. Use ISINSCOPE (as you did) to show/hide each measure only at the right level.
3. Place these measures into Values of the Matrix → now you can set alignment & conditional formatting individually.

⚠️ If you keep everything inside one measure, you won’t be able to control formatting separately.

[MFelix · Accepted solution]

Hi @nandanw ,

Since you have the values has a single measure you cannot define the alignement for each one since the formatting is for each value that you have in your calculations I assume that you went with a single measure in order to get a specific layout.

Concerning the condittional formatting what you need to do is to apply the condittional formatting using also a measure similar to the one of the combined measures but instead of having the dax call the measure you need to get the color coding you need so something similar to this:

Color formatting = 
SWITCH (TRUE(), 
 SELECTEDVALUE(Table[Name]) = "Actual" && [Actual] < 500, "#123556",
 SELECTEDVALUE(Table[Name]) = "Actual" && [Actual] < 1000, "#863132",
 SELECTEDVALUE(Table[Name]) = "Actual" && [Actual] > 1000, "#488313")

Table [Name] is the column you are using on the switch measure to determine what is the calculation to be called, the #123456 are different colors I have just added random numbers (not sure of the color) but you can always check the color codes in any formatting option in PBI or other tool.

This can have a more complex syntax in order to get all the condittional format you need.