ADVANCED Matrix Formatting I ALTERNATE Column or Row COLOR in Power BI

In this video I show you how to improve the design of a table or matrix visual with a little bit of DAX. You will learn step by step how to take your table to the next level and alternate the background color by column or row. Advanced table formatting.... here we go!
Enjoy this video and subscribe to always stay updated on my favorite Power BI tricks :)
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly [e-mail]
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
00:00 Intro
00:32 Standard Color Banding
01:39 Color Banding with DAX - Intro Example
05:11 Using MOD() for conditional formatting
06:41 Using RANKX for color banding
12:38 Color banding with multiple levels
16:03 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO​
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on KZbin: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI​ #PowerBI​ #DataTraining​
#powerbidesktop​ #powerbitraining​ #powerbideveloper​ #DAX

[@PeterKontogeorgis]

Great content as always Bas! Would love to see a video where you show some tips on how to create a pretty table/matrix.

[@pam8867]

Wow! Awesome video. You explained it so easily!

[@Borka-et6we]

Great video! But if I want to format the whole row (including the header to the very left)? I have a P&L with only one measure in the values. It is both account groups sums, gross margin, gross margin%, EBIT and EBIt%. But all in one measure thanks to a switch formula. But now I want to differentiate these different rows.

[@dantepraxedis]

Good video, you are improving Power BI where it needed most: Lok and feel. I think Power BI should have this features out of the box.

[@eduardosousa8573]

Great content !!! You help me last month, and you don`t know that! : ) Greets from Brasil !!

[@emmafeng55]

Quick and easy. thanks Bas! :)

[@gnavarrolema]

Great video...thank you!!!

[@aaronr3662]

Brilliant teacher! Thanks

[@vignesversb]

As always very creative Bas 👌🏼😊

[@JovialMalibidaN]

Wonderful video!

[@tubeitz]

awesome! thank you

[@unnikrishnansanthosh]

excellent , thank you BAS

[@Tyraelxadrias]

Wow awesome video!

[@susanclark1778]

This is a great idea - but I think since I have my columns as rows - I can only seem to get this to highlight rows and not columns. So close! Thanks for all you do!

[@GentilOliveira]

Fantastic!

[@xxx-hx2yp]

Thank you for this great video, please how did you get months/year on columns knowing that generally dates are stored in one single column ?

[@samiraslan9735]

Hello Bas! I love your videos and your way of delivering the info. I have a question, what if the table/matrix had a field parameter? Can you format it?

[@lukeminikoski]

Excelente!

[@mnowako]

Impressive!

[@joaquimcosta952]

Very nice.

[@mathew9665]

Hi Bas nice video and approach to the solution - Question if I may is there a way to do a Top/Bottom N - that dynamically has the total of the remainder?

[@3danim8r1]

Nice one thanks for sharing...the table you have shown if we have different condition for each column in matrix how do we apply conditional formatting on that.

[@mostafaelsawy1209]

Thanks a lot

[@user-jx5xp3mb4k]

Hi Bas, thanks for putting together this video. That is a very useful tip. One question - How do you work out the ranked value of the column if some of the rows have a blank value? Also, if there is no product dimension table and only has on single Sales fact table in the data model?

[@kassiowifried]

Good job

[@JLeon9376]

Knap Bas 👌🏼!

[@suzannenierop3179]

Hello Bas, do you also have an explanation how to change the background color if you have switched the values to the rows? But still want the date (columns) to be colored? Thank you!

[@ongz4975]

Awesome! Anyone know how to format the rows in a matrix individually?

[@phillipemenchaca1859]

Question - This is a great video that has helped me on multiple reports over the last few months, but how can I remove or use alternate color on the total column. I notice it is usually labeled as a "1" and if the total columns are odd, it then colors the last and the total. If i switch to ISEVEN, then the last two are left with the same color as well. Much appreciated!

[@muralimohan71]

Thank you 😊 Is it possible to change the font color of a cell value of particular column based on same row previous column value in Matrix visual. For example first row first column value is 100, and if first row second column value is 200 then it should show green color font in second column, if it's less than 100 then red color. Is this possible with DAX provided there is a hierarchy at column level naming also as Year, Quarter and Month.

[@ahmetemin1721]

Thanks Bas :)

[@HowtoPowerBI]

That is so nice!!! Thanks for ur support!!! 😄

[@gmartinezm]

Excellent, I have a question, what if I want to keep in a Matrix always the same columns i.e months of the year, and I want to display information like 0 when no data, etc. My understanding is that the columns are getting generated from existing data automatically. But not sure how to visualize all the months and not just the ones with sales. Thoughts?

[@letiziabellavia2488]

Hi, how can I set conditional formatting only for a part of a mesure which is concatenation of two? Ex: Mesure = [ISA] & [ISA VARIANCE], and I'd like that in my table only [ISA Variance] is colored according to a conditional formatting. Thanks

[@harshbadgujar2142]

Hey, i love your work. I had just one query , can you please share how you made this matrix that you show in video at 1:04 timestamp. It would be great to see step by step tutorial for that

[@sudhirsimson3340]

Nice video. Thank you. I want to ask for another possibility. I use a "Text filter" custom visual to filter a column and show the info in a table. How can I format the text in the table with a specific font colour (say blue) only for the Keywords used to search? I tried using CONTAINSTRING but cannot pass the "Find Text" parameter from the User Input text visual-any suggestions? The purpose is for the user to see the text they have searched easily. In some cases, it would filter multiple columns, so it would be easier if we have specific font colour or at least the background colour highlighted.

[@mindortv8462]

Very good video But I have a question. What if I have some blank spaces in that matrix? How can I give them bg color as well?

[@vinodreddy1238]

Can we apply background colour to row headers in matrix visual?

[@kuldippandey9672]

this was great, Bas! : I want something related to Conditional formatting on a Matrix, but it should be individually applied to each subtotals sections. i.e. in this video for "Cost of Good Sold" (Top, Middle,Bottom) and for "Operating Expenses" (Top, Middle,Bottom) it should work separately. Could you please help

[@magnusvangekjrchristensen1996]

Hi Bas, Great video! I have a problem with conditionally coloring cells with blank values. Cells with value work fine, but not blank ones. Hot can I solve this? Thank you!

[@ahmedabbas-mk2wb]

I would like to know if it's possible to add background for each value individually in the column in 7 minutes and 45 seconds. Can you provide a solution?

[@BeHappii2]

I have an inquiry regarding setting up a logic in Power BI that allows users to select colours based on Excel input data, affecting the colour of column headers in a dynamic fashion. The idea is for the system to recognise numerical inputs - with '1' representing green, '2' signifying blue, and '3' indicating red - and to modify the column header colour accordingly. The dataset is updated on a monthly basis, which calls for a logic where changes made in Excel are seamlessly integrated and mirrored in Power BI. My focus is solely on columns, as my column tables don't include rows. Despite the unique nature of this user requirement, which has kept me puzzled for nearly a week, I'm reaching out to see if you could provide guidance or perhaps produce a video tutorial on this topic.

[@sunilsasidharan8614]

Hi, could you please do a video where you can create several rows to a matrix. I have a requirement where I have to create different columns for different variables, example, No. of projects, Project phases, Project total Sales, Project incremental sales. When I tried, adding these to Rows, not all values are reflecting in the Matrix Visual.

[@shivasai25]

Hello, How can i achieve this. How can i filter multiple columns with same colour like Red 🔴, Green 🟢. By that i can have a bookmark by selecting green, then it will show only green colour labels in whole matrix.

[@vaibhavwankhede8691]

I need same but I want to shows data Month(which are column axis) wise and whatever % and $ is in different column, keep in same column can we do this ? example Jan Sale 77.0 % Sale 100% Gross Margin 55.0 % of Gross Margin 71% and So on But % sale are in different rows not in hierarchy

[@tornikevadachkoria3320]

Its possible put 2023 with other dates(columns)? I need put 3 columns distinct of 2022 in 2023 and I dont know how to do.

[@LvUhcX]

how did you get this layout?? my power bI looks different!!

[@muhammadusman-lj3de]

"Specific Column" is greyed out for my matrix. any advice please

[@jimmy3862]

Hi Bass. Incredible as always! Thank you so much for your time and effort to teach all of us a little more! Now I have question! I don't know if you already talked about this before, but I want to have a Matrix (because I need to use levels of rows) that the COLOR of VALUES (In Columns) changes according to a Slicer selection. eg. Slicer I have Billings selected, then the color of VALUES in Matrix will be light green. Now I have "Revenue" selected in the slicer and color in matrix should be light red. Is this approach possible? I've tried many things, but all failed. Thanks in advance again!

[@jimmy3862]

@Bass, I just GOT IT. Think a little harder, adjust your solution and BANG! It's working! AGAIN! Thank you so much!

[@ginger.schooling]

I also have this problem and don't know how to fix it. I want the color-banded columns to work even when people filter the report with slicers. When they use slicers, the banded-columns are sometimes the same color next to each other.

[@abdullashaik4902]

Can we have an image +text in the same Matrix column?

[@PJ-ep3hk]

Thanks Bas! How do I create the same bold rows (text & values) showed in your example template like Sales, Costs of Goods Sold, etc? Thanks Paul

[@PJ-ep3hk]

Just noticed that you made use of the subtotals, which is part of the regular formatting functions of PBI. Thanks again!

[@user-jx5xp3mb4k]

Hi Bas, I have one more question. I can see in your tutorial that all cells in the alternate columns are formatted in Lightblue, including the cells that have empty values. I followed your tips, but my formatting is not working perfectly as some cells with empty values in the Lightblue columns are not formatted as expected, remaining in white. Do you know any reason why it happened that way? Any suggestion on how to fix it?

[@gloriaz3711]

I also had the same question as you

[@MinaJosephBoulos]

Hey man, thanks for the video, I followed all the steps, but in your video the blank values are formatted, but in my matrix they are left white. Can you advise?

[@lindajones4799]

I have the same problem. Perhaps the latest update changed this?

[@j.payeras7035]

Hi, I downloaded the file of the video to check the same. To avoid the blank Blackground he did this on his Total Sales measure: Total Sales = VAR Sales = SUMX( fctSales, fctSales[Quantity] * fctSales[Price] ) VAR Result = IF( ISBLANK(Sales), "", Sales ) RETURN Result

[@rahulbankey4009]

Hi, Just one question. At 11:07 can we even bring the column headers in blue color???

[@HowtoPowerBI]

with the table visual you can (apply to header), this is unfortunately not an option for the matrix visual

[@johnnytech5130]

are we going to skip over the part where you had 2 values under one column at the same time? how did you do that?

[@shobhavenkatesh20]

Hello Bas, How did u split Jan column into $ and %?

[@domingoscosta2721]

He used two different measures and the Month column. Hope this finds you well and hopefully you don't need this anymore

[@Velsrojs]

Nice tip. But you are not showing how to format every other/alternate ROW /(as it says in the title).. only alternate columns. So how to do this if I want to format alternate rows?

[@greatzto]

You just use the field for rows to replace the field name in the custom measures. For example, in the video, the author used dimProduct[Product Name] as column and dimCustomer[Customer Name] as rows in the matrix. He created "CF - Product" to get the alternative column background. If you make a new measure called "CF - Customer" by replacing dimProduct[Product Name] with dimCustomer[Customer Name], and apply conditional formatting based on that, you will get alternative row background.

[@MrBodies07]

Looks great but unfortunately I'm getting a very unhelpful syntax error - "The syntax for ')' is incorrect." (I'm not missing any brackets - Syntax is the same) :(

[@MrBodies07]

I had included an empty return statement :)

[@normanhelfand8260]

thanks for this. But Microsoft should not make it so complicated to do some pretty standard table formating😢

[@karolmarian8987]

Great video, but as always - doesn't that just terrify you guys that thing that is 3 clicks in Excel now takes that long to make it work? I really fail to understand why PBI is so popular

[@HowtoPowerBI]

thanks :) power bi has features likes charts interactions, custom tooltips and many others that you don't have in Excel. Having an excel report is not great to be shared throughout your organization in terms of sharing options and security. That's where Power BI shines and is therefore more suitables for building reporting solutions in an organization.

[@karolmarian8987]

​@@HowtoPowerBI Hey, thanks for the answer :) I just want to say that I really enjoy your videos - they are the ones I have found so far! My problem with PBI is how long it takes to build report so results look "decent". From my experience, PBI cannot produce visuals as attractive as e.g. Think-Cell (what is the most important for senior leaders, they will never-ever use charts interactions, tooltips etc. - they need good representation of numbers that they can understand in seconds). For ad hoc analysis it takes forever to build something in PBI and data validation part is just very difficult (here Excel rules - of course with easy access to ERP source, not as manual typing exercise). In terms of security - direct control in ERP rules over PBI by far. On top of that, as every team builds their own reports etc. we end up in situation of having 25 "demand" metrics - so shareability is more of a risk than an opportunity. But maybe I am biased :) I definitely have to learn more of it, as I need to build my reports - but I am simply surprised how PBI is praised :)

[@oleg.ostrokomorets]

Слава Україні!