The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi All,
In my report i have a table and the data types are different (have $,%,text, numbers in a single column) in columns, i need to add a backround color for the data, any leads please
Many thanks in advance!
Solved! Go to Solution.
Hi @Balaji1414 ,
Sorry for delay. I updated my sample pbix file(see attachment), please check whether that is what you want.
Conditional formatting =
VAR _curcolumn =
SELECTEDVALUE ( 'Table'[Q1] )
VAR _includesc =
IFERROR ( SEARCH ( "$", _curcolumn, 1, 0 ), 0 )
VAR _scnumber =
IFERROR ( VALUE ( RIGHT ( _curcolumn, LEN ( _curcolumn ) - 1 ) ), 0 )
VAR _includepc =
IFERROR ( SEARCH ( "%", _curcolumn, 1, 0 ), 0 )
VAR _pcnumber =
IFERROR ( VALUE ( LEFT ( _curcolumn, LEN ( _curcolumn ) - 1 ) ) / 100, 0 )
VAR _number =
IFERROR ( VALUE ( _curcolumn ), 0 )
RETURN
SWITCH (
TRUE,
_includesc > 0,
IF ( _scnumber > 500, "#315496", IF ( _scnumber < 500, "#f6af82" ) ),
_includepc > 0, IF ( _pcnumber > 0.5, "#a9d092", "#ffff01" ),
IF ( _number > 75, "#00af50", IF ( _number < 50, "#fe0000" ) )
)
|
Best Regards
Hi @Balaji1414 ,
I created a sample pbix file(see attachment), please check whether that is what you want.
1. Create a measure as below to set the color for different type of data(include $,%,text, number etc.)
Note: You can change the color inside the formula to the color you want.
Conditional formatting =
VAR _curcolumn =
SELECTEDVALUE ( 'Table'[Column] )
VAR _includesc =
IFERROR ( SEARCH ( "$", _curcolumn, 1, 0 ), 0 )
VAR _includepc =
IFERROR ( SEARCH ( "%", _curcolumn, 1, 0 ), 0 )
VAR _number =
IFERROR ( VALUE ( _curcolumn ), 0 )
RETURN
IF (
_includesc > 0,
"red",
IF ( _includepc, "orange", IF ( _number > 0, "purple", "green" ) )
)
2. Make conditional formatting for background color of the column(Format by Field value)
Best Regards
Hi, column output shuld come like this
Hi @Balaji1414 ,
Sorry for delay. I updated my sample pbix file(see attachment), please check whether that is what you want.
Conditional formatting =
VAR _curcolumn =
SELECTEDVALUE ( 'Table'[Q1] )
VAR _includesc =
IFERROR ( SEARCH ( "$", _curcolumn, 1, 0 ), 0 )
VAR _scnumber =
IFERROR ( VALUE ( RIGHT ( _curcolumn, LEN ( _curcolumn ) - 1 ) ), 0 )
VAR _includepc =
IFERROR ( SEARCH ( "%", _curcolumn, 1, 0 ), 0 )
VAR _pcnumber =
IFERROR ( VALUE ( LEFT ( _curcolumn, LEN ( _curcolumn ) - 1 ) ) / 100, 0 )
VAR _number =
IFERROR ( VALUE ( _curcolumn ), 0 )
RETURN
SWITCH (
TRUE,
_includesc > 0,
IF ( _scnumber > 500, "#315496", IF ( _scnumber < 500, "#f6af82" ) ),
_includepc > 0, IF ( _pcnumber > 0.5, "#a9d092", "#ffff01" ),
IF ( _number > 75, "#00af50", IF ( _number < 50, "#fe0000" ) )
)
|
Best Regards
Hey thanks for your input, same way i have implemented it is working fine now.
Here is another one if you can help that woud be great
I have built around 15 separate reports, all the reports are matrix (visual) data where I have displayed last 4quarters of data for each measure, I have defined BG color for the values based on the performance for each measure for the four quarters. Now I need to build a final report from all these 15 reports, I need to get the data from these measures where the measure performance is poor, bad (I have a logic)
Hi @Balaji1414 ,
Not sure completly on what you are trying to ask here.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Thanks,
Pragati
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
139 | |
110 | |
107 | |
76 | |
62 |
User | Count |
---|---|
270 | |
129 | |
123 | |
100 | |
92 |