Skip to main content
cancel
Showing results for 
Search instead 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

Reply
shanco
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:

Matrix1.png

 

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

Matrix2.png

 

1 ACCEPTED SOLUTION

@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.

Please @mention me in your reply if you want a response.

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.
If you found this post helpful, please give Kudos C

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

View solution in original post

6 REPLIES 6
AllisonKennedy
Super User
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...

Please @mention me in your reply if you want a response.

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.
If you found this post helpful, please give Kudos C

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

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?

@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.

Please @mention me in your reply if you want a response.

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.
If you found this post helpful, please give Kudos C

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

Thanks @AllisonKennedy 

This did the trick. I am getting much closer.

 

I now get this:

Matrix2-no-subtotal.png

If I add subtotal I get this:

Matrix2-subtotal.png

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

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.

Please @mention me in your reply if you want a response.

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.
If you found this post helpful, please give Kudos C

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

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...

 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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