Like many others I see on forum and elsewhere, I am having issues with row and grand totals within a Matrix. I definitely need to learn much more about DAX but have searched here and elsewhere and still can’t get my head around it. I believe the Summarize function is needed but logic may have been incorrect.
Any DAX formulas to assist would be greatly appreciated.
I have Total Payroll Hours Paid broken out by Employee within Hospital Job. I want to multiply these Paid Hours by a monthly Burden Rate and calculate Allocated Costs.
You can see from below screenshot that the Individual rows are accurate but the Row and Grand Totals are not. I would like for the Burden Rate and the Allocated Totals to be accurate. However, I may delete the Burden Rate column if inaccurate.
Total Labor Hours Paid = [Total Reg Hrs Paid] + [Total OT Hours] + [Total DT Hours]
Burden Rate for Month = sumx(filter(‘Burden Table’,‘Burden Table’[Date]), ‘Burden Table’[Burden Rate]) NOT SURE THIS IS BEST WAY or IF NEEDED[
Allocated Costs = [Burden Rate for Month] * [Total Labor Hours Paid]
Second issue is I would want any Hospital that had no Paid Hours not to show in visual. Screenshot below shows that the Burden Rate shows on every line. I would prefer to do this within the DAX formula rather than a filter.
I have uploaded a simplied version of the pbix file.
EDNA Example, Allocaion.pbix (455.2 KB)
Again, appreciate any help. Let me know if you need any additional info or have questions.