Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all - is there any way to get a custom calc in the the total row of a matrix visual? I need this visual to include the people in the list who don't have any values.
For example, the 77.17% under the Util column for 12/26/2021-1/1/2022 in the screen shot below (loooks like) its only taking into consideration the people who actually have hours. The average should be about 41%. I need the calc to include the people who dont have any hours at all. There are no records in the underlying table for those people, so I can't 'set null to zero...' or anything like that. The count of people will (most likely) always be the same however.
The formula should be something like Sum (Bill Hours) / count(people in PS)
PS is a department
The People are Employees, the projects they worked on are underneath in the roll ups
Bill hours is how many billable hours they worked on the project
Util = DIVIDE ( SUM ( worklogs[BillHours] ), [WorkHours] )
Worklogs are the records in the underlying table that show how many hours they worked on the project.
For the employees that don't have any values, there are no worklogs for them in the table.
Please let me know what add'l info you need.
Thanks in advance!
Solved! Go to Solution.
Hi, @ctedesco3307
Have you tried the solution provided by @PaulDBrown to change the total value?
You can refer to these silmilar threads.
Matrix-total-subtotal-row-customization
Totals and Subtotals in Matrix
For some reason I can't download your sample. If the problem isn't solved, please share your sample data and expected result in Excel for further research.
Best Regards,
Community Support Team _ Eason
Looking for some more specific assistance with the ideas referenced above
Below is a lnk to my sample data that I believe has all the categories required, but I'm stuck on how to put them together to get the right average I need.
Workhours is how many hours the employee is available during the day
Bill hours is the amount of time the employee spent on that issue Key
Util is the % of time they worked that was billable
Util is a measure
Hi, @ctedesco3307
Have you tried the solution provided by @PaulDBrown to change the total value?
You can refer to these silmilar threads.
Matrix-total-subtotal-row-customization
Totals and Subtotals in Matrix
For some reason I can't download your sample. If the problem isn't solved, please share your sample data and expected result in Excel for further research.
Best Regards,
Community Support Team _ Eason
To show a different result for the totals you can use the function ISINSCOPE.
So it would be something along the lines of:
Different total = IF(ISINSCOPE(Table[column]), [measure 1], [measure 2])
Proud to be a Super User!
Paul on Linkedin.
@ctedesco3307 This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also, MM3TR&R may help: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |