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.
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
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!
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
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.
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.
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!
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?
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.
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
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!
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?
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?
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.
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!
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?
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?
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
Hi, Great to see your videos and lot of cool and great stuff.. I am exploring one option, but I am unable to find the solution. What I am looking for is I like to add icon like emoji or something (same for all rows) before +/- field of the rows so my row field order will be emoji field (emoji will be repeated for all the rows and) and then my main category fields which will have expand or collapse icons (+/-). Please suggest.
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.
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
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.
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?
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!
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.
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?
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?
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.
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) :(
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
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.
@@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 :)
![I.N "HALLUCINATION" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/n5B5q1Hwt_U/mqdefault.jpg)
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.
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.
Great content as always Bas! Would love to see a video where you show some tips on how to create a pretty table/matrix.
Thanks Bas :)
That is so nice!!! Thanks for ur support!!! 😄
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
Great content !!! You help me last month, and you don`t know that! : ) Greets from Brasil !!
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 ?
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?
Awesome! Anyone know how to format the rows in a matrix individually?
Brilliant teacher! Thanks
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!
Wow! Awesome video. You explained it so easily!
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
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.
Hi, Just one question. At 11:07 can we even bring the column headers in blue color???
with the table visual you can (apply to header), this is unfortunately not an option for the matrix visual
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.
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
Just noticed that you made use of the subtotals, which is part of the regular formatting functions of PBI. Thanks again!
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!
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?
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.
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
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!
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?
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?
As always very creative Bas 👌🏼😊
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.
Can we have an image +text in the same Matrix column?
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.
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!
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?
Quick and easy. thanks Bas! :)
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?
I have the same problem. Perhaps the latest update changed this?
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
Hi, Great to see your videos and lot of cool and great stuff..
I am exploring one option, but I am unable to find the solution. What I am looking for is I like to add icon like emoji or something (same for all rows) before +/- field of the rows so my row field order will be emoji field (emoji will be repeated for all the rows and) and then my main category fields which will have expand or collapse icons (+/-). Please suggest.
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.
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
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.
Can we apply background colour to row headers in matrix visual?
Great video...thank you!!!
"Specific Column" is greyed out for my matrix. any advice please
how did you get this layout?? my power bI looks different!!
Wonderful video!
Wow awesome video!
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?
I also had the same question as you
excellent , thank you BAS
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?
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!
@Bass, I just GOT IT. Think a little harder, adjust your solution and BANG! It's working! AGAIN! Thank you so much!
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.
awesome! thank you
Fantastic!
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?
Very nice.
Thanks a lot
Hello Bas,
How did u split Jan column into $ and %?
He used two different measures and the Month column. Hope this finds you well and hopefully you don't need this anymore
Impressive!
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?
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.
Good job
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) :(
I had included an empty return statement :)
Knap Bas 👌🏼!
Excelente!
Sir aap kaha the.🎉
thanks for this. But Microsoft should not make it so complicated to do some pretty standard table formating😢
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
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.
@@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 :)
Слава Україні!