Count Total Rows while Ignoring Column Filters

[Namdu_PAU]

Hey all,

Intermediate user to PBI here asking for help around a calculation.

I am currently trying to summarise training results for the business and have 3 connected tables:

My task is to (for every trainee) calculate the % completed for each training group.

My approach to this was to create a table and use SUMMARIZE.

However, I am having trouble with the calculation for the total # column.

I would like the "Total # Courses" to display the total number of available courses for that training group.

For example, the first 3 rows, Total # Courses should be: 31, 43 and 11 respectively.

However instead it is simply showing me a filtered count based on User Name.

It feels like such a simple DAX code but I simply cannot work it out.

Any help is sincerely appreciated.

Regards,

Namdu

[Namdu_PAU · Accepted solution]

I have managed to solve it myself!

The DAX Code is: 

"Total # Courses", CALCULATE(COUNTROWS(Curriculum),ALL(Curriculum),VALUES(Curriculum[Training Group]))

It is now correctly counting the total number of courses within the group. My problem was using "ALLSELECTED" or "ALLEXCEPT", these filters kept the row context filters. I had to first use the ALL function, and then specify the values.

[Namdu_PAU]

It looks like the previous link deletes the file after it has been downloaded once.
Use this instead: https://www.dropbox.com/scl/fi/e07bdgdvzg4m5j52susi8/PBI_Help_Counting-Training-Courses.pbix?rlkey=w...

[Ritaf1983]

Hi @Namdu_PAU 

Please provide a workable sample data and your expected result from that. It is hard to figure out what you want to achieve from the description alone.  

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[Namdu_PAU]

Hi Rita,

Thank you so much for such a quick reply!

I thought I was being really clear, but that is okay, I have created mini datasets that I am able to share - please see link to pbix: https://file.io/Oqohb6riIWQB 

As for what i am trying to achieve:
Cert 1 training group has 31 total courses, however the table is counting 52.

I would like to have a total count for every single training group.

[danextian]

The links you've posted are all unavailable. Please use dropbox, google or onedrive.

[Namdu_PAU]

Hey Danex,

Yep sorry, I was on my work PC and didn't have access to the other sharing platforms. Please see the link below:

https://www.dropbox.com/scl/fi/e07bdgdvzg4m5j52susi8/PBI_Help_Counting-Training-Courses.pbix?rlkey=w...