Forum Discussion
Count Total Rows while Ignoring Column Filters
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
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.
6 Replies
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
Super User
The links you've posted are all unavailable. Please use dropbox, google or onedrive.
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...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.
