Tables And Matrices in Power Bi

Lets discuss how tables work in Power Bi

You can create your own tables from your data like in Microsoft Excel . But in Power Bi you can do that and beyond , and its a lot more fun .

You can find the table visualization on the “Build Visual” under “Visualisations” Pane and build a table from the data you have loaded into Power Bi . Suppose your data file is a “jobs in a company " csv file which tells you different job openings (job titles) in a particular company and their department , manager in charge , yearly salary , job type , experience required with the date the job was posted .

Now you are expected to build a table visualisation from this data csv . Lets understand how to do that . The first thing is to decide what all fields from the data you want as the columns of your table . For the table here we need the following columns :

1. Job Id
2. Job Title
3. Job Type
4. Department
5. Salary
6. Experience. Select the Table visualisation from the visualisation pane and add the respective fields to the “Columns” field of the table (make sure you rename the fields to make it more readable)

You can always sort ID columns in your table in ascending order by just clicking the small downward arrow under the column name .

Once you have made your table , there are more you can do with it . One impressive thing is to apply “Conditional Formatting "

Conditional Formatting

You can apply 4 kinds of conditional formatting to each column value you have selected , to select the conditional formatting you need to click on the drop down arrow on the left of each column field and select it . The four kinds of conditional formatting you can do are :

• Background color
• Font color
• Data Bars
• Icons
• Web Url

Data Bar gives you a bar of size in correspondence with the numeric value (Data Bar formatting is only applicable for numerical columns) .Here in the example image given below , it is applied “yearly salary " column by the following steps:

1. In the Visualizations → Values area, click the dropdown arrow on Yearly Salary → Conditional formatting → Data bars.
2. In the Data bars dialog:
   • Format by:
   • Rules (Power BI will expose min/max options) or leave default.
   • Set Minimum and Maximum if you want a fixed scale (e.g., Min = 40000, Max = 150000). Otherwise use Min and Max (auto).
   • Choose Bar color (pick the blue you want).
   • Toggle Show bar only = Off so the numeric value still displays (this gives the bar + value like your screenshot).
   • Optionally enable a border or negative bar color if needed.
3. Click OK. The table will now show a left-to-right bar in each YearlySalary cell proportional to the number, with the currency value shown at the right.

Tips :

• If your YearlySalary is formatted as a number but displays as $82,000, set the column Format to Currency in the Modeling tab so that the value and bar look correct.
• To fix the scale across pages, explicitly set Min/Max numbers in the Data bars dialog.

“Icons” formatting can be applied to numerical and categorical columns by defining the “Rules” as “Format Style”. Lets try and understand the Icon Dialog Box and the fields in it first :

• Format style → Rules
  You are writing explicit rules (text or numeric comparisons), not using numeric buckets. Rules support is, is not, contains, does not contain, starts with, ends with, and numeric operators when the field is numeric.
• Apply to → Values only
  Where the formatting will appear (values, totals, or both).
• What field should we base this on?
  The field whose value will be evaluated by the rules. It can be a text (categorical) field (like experience) or a numeric field. If that field is not present in the visual, Power BI will use the summarized value (see Summarization).
• Summarization → First (or Sum, Min, etc.)
  When a field has multiple values for the same visual cell (because of aggregation or grouping), this determines which value is used for the rule. For categorical fields First or Last are commonly used.
• Icon layout / Icon alignment
  Where the icon appears relative to the cell text (left/right/top) and how it aligns.
• Style → Custom
  Lets you choose which icon graphic to use for each rule (diamond, triangle, circle, etc.) and pick colors.
• Rules section (the rows you created)
  Each rule is evaluated and when a rule matches, the chosen icon is applied. Example rows in the table example given below:

  • If value is Intern → red diamond

  • If value is Entry → yellow triangle

  • If value is Mid → green circle

  • If value is Senior → purple circle

Important: rules are evaluated in order, and the first matching rule is applied. If nothing matches, no icon (or a default) is shown.

Here in the example image given below , it is applied “experience " column by the following steps:

1. Click the dropdown next to Experience in Visualizations → Conditional formatting → Icons.

2. Set Format style = Rules.

3. What field should we base this on? = experience.

4. Summarization = First (or whichever makes sense).

5. For each row in Rules:

   1. Select operator is

   2. Enter the category text (Intern, Entry, Mid, Senior)

   3. Pick the desired icon and color on the right.

6. Set Icon layout and Icon alignment per your preference.

7. Click OK.

Lets discuss how matrices work in Power Bi

A Table gives you flat data, while a Matrix gives you summarized, hierarchical, pivot-style insight. You can find the matrix visualisation in the “Build Visualisation” Pane under Visualisations

Here we are trying to recreate the matrix given in the image below. The matrix is a pivot-style report of open jobs by Department which expands to show the experience levels . It has three columns :

1. Total Job (count)
2. Median yearly salary
3. Job Posted (sparkline)

Now lets build the matrix :

1. On the report canvas, insert a Matrix visual.

2. In Rows drag Department and, to make them expand into sub-rows like Entry/Intern/Mid/Senior, drag Experience under Department

3. In Values add:

   • Total Jobs (job_title column from the “jobs in a company” csv file and set aggregation to count and rename field to total jobs )

   • Median Yearly Salary (yearly_salary column from the “jobs in a company” csv file and set aggregation to median and rename field to total jobs )

4. Turn off or on subtotals as desired (Format pane → Subtotals). Set the matrix to show row headers and totals similar to the screenshot.

Now the third column is a actually a sparkline named “Job Posted” , let’s see how we achieve that :

1. Make sure your matrix is selected. Go to the “Insert” Option in the menu and choose “Add a sparkline” from the ribbon.

2. In the Sparkline dialog:

   • The selected numeric field is pre-populated as the Y. Here we need the count of job titles , select job_title and sent summarization to count.

   • For the X, choose your date field (PostedDate) ,choose the proper summarization (Month, Week, Day) depending on desired granularity.

   • Create the sparkline; it will be added as a new column to the matrix.

3. To format the sparkline: click the sparkline column dropdown → Edit sparkline (or use the Format pane → Sparklines card) to change line vs column, markers (first/last/high/low), line width, and marker styles. (Microsoft Learn)

Important limits & notes: Power BI supports up to 5 sparklines per visual and up to 52 points per sparkline; also visuals with sparklines have some matrix column/row limitations(see the docs if you plan lots of sparkline columns. (Microsoft Learn))

Now we need to do some conditional formatting in the median yearly salary ; let’s add Data Bars to it:

1. In the Fields area of the visual, click the dropdown on Median Yearly Salary (or right-click the field in the matrix).

2. Choose Conditional formatting → Data bars. (Microsoft Learn)

3. In the Data bars dialog:

   • Leave Show bar only unchecked if you want both the bar and the numeric value (that matches your screenshot where value appears to the right of the bar). If you prefer only the bar, check Show bar only.

   • Set Minimum / Maximum (Auto is fine; you can specify custom min/max to control scaling).

   • Pick bar color, direction and axis style.

4. Click OK.

… and voilà!

Now try creating your own table and matrix visualisations on your data !!All the best!