![]() This cookie is set by GDPR Cookie Consent plugin. These cookies ensure basic functionalities and security features of the website, anonymously. Necessary cookies are absolutely essential for the website to function properly. If you would like more information about our courses, get in touch with our team on 0800 0199 746. We cover a range of visualisations on both our Fundamentals and Advanced courses, meaning you can get in depth knowledge on exactly how to use these functions to your advantage. Matrix Visuals in Power BI are extremely useful when it comes to displaying information clearly and wanting to drilldown into the numbers. Just using a simple background color brings the visual to life. See how much more appealing and informative the coloured chart is. Secret No.7 – Use Conditional Formatting.Ĭompare the 2 visuals below. However, just to say that if there is more than one dance in a particular week, then the dance won’t show so this is why I needed another Matrix to show multiple dances per week. In the brackets of the SELECTEDVALUE function, put the column name of the column you want to put in the middle of your Matrix and use this measure in the Values bucket. You can see that in Rows is Contestant names, in Columns is the Week and the values area is populated with the names of the dances performed.įor this you need a simple measure as follows: – Dance Name = SELECTEDVALUE(Dances) Secret No.6 – Show text in the Values area.Įver wanted to put the names or descriptions into the values area of the Matrix, as in the example above. This is a well-known secret, but all you need to do is expand the Values card and select “Show on rows” On the right, the values have been placed in the Rows. On the left, the Values are in columns, which is the default. I found “trial and error” worked well here! Secret No.5 – Show Values on RowsĮver wanted to do this? Compare the two Matrix visuals below. ![]() Then select the field(s) that you want to show Subtotals or Grand Totals for (note the Grand Total field is listed first). On the Subtotals Card, at the bottom, there is a “Per Row Level” and a “Per Column Level” option. So how do I control which Subtotals and/or Grand Totals to show? This secret is really hidden away where no one can find it! ![]() If I turn subtotals on, I get subtotals for both Professionals and Judges, showing the same values. I now want to show the subtotals, but only for the Judges’ Scores. for the Judges, things become a little more challenging. 1 above.) Secret No.4 – Only show the Subtotals/Grand Totals you want to see. This is definitely an improvement, but I still see those subtotals and they’ve now moved to a row of their own!Ĭlearly, I can now just turn off the Row subtotals on the Subtotals card (as explained in Secret No. “Compact layout” of the Excel Pivot Table). To do this, just turn off “Stepped layout” on the Row headers card (cf. It’s better to put each Row label in a separate column. With most Matrices, the indented or “Stepped” layout gives less clarity to which rows the values refer to. More secrets need revealing! Secret No.3 – Turn off “Stepped layout”. The Professional names are indented under each Contestant when they really should be sitting beside each other, and because there is only one Professional for each Contestant, I get irrelevant Subtotals on the Contestant rows (in bold). Looking at the Matrix above, not exactly what I want though. Apparently, this is what you need to click on to see the second row. There is one label “Expand all down one level in the hierarchy”. Unfortunately, when you put a second field into Rows, it may appear that nothing much has happened secretive or what! However, you’ll notice a series of arrows at the top right (or bottom right) of the visual. You’ll see below that I now have both Contestant and Professional in Rows. The starting point to understanding this secret is to appreciate that it’s not until you work with multiple fields in the “Rows” bucket that your Matrix can become more informative. Secret No.2 – Expand all down one level in the hierarchy. To remove only the Grand Total while retaining other Subtotals, you’ll need to discover another secret, No.4 below. However, be warned if you have multiple fields in the Rows bucket, this will remove all your Subtotals, not just the Grand Total. Where is this option hidden? Well, one thing you must understand is that in a Power BI Matrix, Grand Totals are really Subtotals! So to turn Grand Totals off, use the Subtotals card. In a Power BI Matrix, you might think you’d be able to do the same thing on the Grand Totals card, but you won’t see any option to turn them off. In an Excel Pivot Table, Grand Totals are easily removed (just use the button on the Ribbon labelled “Grand Totals”). ![]() Secret No.1 – Grand Totals are really Subtotals. With this in mind, here are my seven favourite Matrix secrets.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |