cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Total number of rows in a Matrix based on column 1

I am trying to build a report which we use to show one of government agencies how many employees were trained by a specific trainer. I have built the report using the Matrix vizualization, but I just need a count of the number of employees they trained in the far right column. I would solve this in Excel with a sumif calculation. The Matrix is setup with the Trainer and the Employee in the Rows section and the Skills are under the Values section. Here is what I need to build:

Additionally, is there a way to do it this way?

1 ACCEPTED SOLUTION
Super User
@shanco

Keep employee in Rows, but also add to Values (you can drag it from the fields pane directly).

Otherwise try the measure I suggested.

You will want to choose 'count' as the option.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

6 REPLIES 6
Super User
@shanco

How far have you gotten with this?
You should be able to add Employee into the Values as well as Skills Tested and that should give you the count, and the subtotals will give you the 3, 2 values you want.

Otherwise you can make an explicit measure:

Count Employees = Count(table[employees])

The sumif will be done automatically by the matrix visual.

You can play with the formatting of the matrix, such as adjusting the stepped layout or turning it off to get the last image you want:
https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual#stepped-layout-with-matrix-v...

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Frequent Visitor

Thank you @AllisonKennedy

When I move Employee down to value, I only get these options - First, Last, Count, Count (Distinct). How do I get it to show all of the employees?

Super User
@shanco

Keep employee in Rows, but also add to Values (you can drag it from the fields pane directly).

Otherwise try the measure I suggested.

You will want to choose 'count' as the option.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Frequent Visitor

Thanks @AllisonKennedy

This did the trick. I am getting much closer.

I now get this:

If I add subtotal I get this:

Is there a way to remove the attempt to total the Skills Tested column? I just want the count column to be totaled.

Super User
You can either create a custom measure for Skills tested so that it doesn't try to total when more than one employee is selected (search measure totals problem by @Greg_Deckler) or move Skills Tested to Rows.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Frequent Visitor

I solved the last part of hiding the totals for the text columns by using the Field formatting option as described at: https://radacad.com/removing-the-total-value-for-a-column-in-the-table-visual-of-power-bi-using-isfi...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors