Forum Discussion
Total Row counts by category
Hi
I have the data shown below: Which holds the user's timesheet data. Each user has 52 timesheets per year, and it shows how many are submitted on time or not. For total timesheet count, i have used the below DAX
Total Timesheet Count =
CALCULATE( COUNTROWS('Timesheet'), FILTER(ALLSELECTED('Timesheet'),'Timesheet Location'[Timesheet Submitted on Time]<>"ABC"))
However, the total timesheet count does not appear as 52 if I only filter the timesheet data that was not submitted on time. The data displayed in the Power Bi table is below.
I need the following outcomes:
Hello pankajgurav
Thank you for providing a sample dataset and demonstrating the expected results. The following DAX measures can be used
To calculate the total timesheets:
Timesheet Count = SUM(Timesheets[No of timesheet])To calculate total timesheet by username (irrespective of sumbitted or, not)
Total Timesheets = CALCULATE( [Timesheet Count], ALL(Timesheets[Timesheet Submitted on Time]) )The following GIF demonstrates various slicer selections; however, total timesheet count remains unaffected by the selection of "Y" or "N".
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTreeProud to be a Super User
4 Replies
Hello pankajgurav
Thank you for providing a sample dataset and demonstrating the expected results. The following DAX measures can be used
To calculate the total timesheets:
Timesheet Count = SUM(Timesheets[No of timesheet])To calculate total timesheet by username (irrespective of sumbitted or, not)
Total Timesheets = CALCULATE( [Timesheet Count], ALL(Timesheets[Timesheet Submitted on Time]) )The following GIF demonstrates various slicer selections; however, total timesheet count remains unaffected by the selection of "Y" or "N".
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTreeProud to be a Super User
Hi quantumudit ,
Thanks for your reply. I calculated a total of 52 timesheet entries using the below formula
Total Timesheet Count = CALCULATE( COUNTROWS('Timesheet'), FILTER(ALLSELECTED('Timesheet'),'Timesheet'[Timesheet Submitted on Time]<>"ABC"))
I have applied the identical formula that you have provided, yet the outcome remains unchanged.
Thanks,
Pankaj
Thanks
Hello pankajgurav
I trust you've used a measure rather than a calculated column. If the solution is still not working, we'll need to examine the data model. Sharing a sample dataset and details about any existing relationships would be extremely helpful.
I've also attached the Power BI file I used to address your query. Please review it and let me know if you see anything I might have overlooked.
Hi pankajgurav - Your issue is that when filtering only "N" (Not Submitted on Time), your Total Timesheet Count column is also being affected by the filter. You need to modify your DAX measure to always return 52 per user, regardless of any applied filters.
Total Timesheet Count =
CALCULATE(
COUNTROWS('Timesheet'),
ALLEXCEPT('Timesheet', 'Timesheet'[Username])
)check the above , i am using allexcept function.
Hope it works.
