The issue that I am dealing with is that I have a sum as the grand total for the value of a ratio of two measures and I need the grand total to be an average. After some research, I am fairly certain that I know the issue is that I am using sum() as part of one of the measures in this ratio, which means DAX is just going to use a sum by default for any subtotal or grand total, but I'm at my wits end trying to figure out how to adjust my formulas to calculate the average instead. To start, I'll show the matrix I am working with:
My formula for status counts per day is
Status Count per Day = [Statuses]/[Days worked]
Statuses (which I think is the source of my problem), unfortunately has the following obnoxiously long code:
Statuses = if(values(TMD[Specialty])="Hunt PS",calculate(SUM('Status Counts'[Count]),
'Status Counts'[TI-Status Combo]="1538 Attempted" ||
'Status Counts'[TI-Status Combo]="1538 Confirmed - All Docs" ||
'Status Counts'[TI-Status Combo]="1538 Confirmed - No Docs" ||
'Status Counts'[TI-Status Combo]="1538 Confirmed - On Hold" ||
'Status Counts'[TI-Status Combo]="1538 Confirmed - Some Docs"),
calculate(sum('Status Counts'[Count]),
'Status Counts'[TI-Status Combo]="1538 Attempted" ||
'Status Counts'[TI-Status Combo]="1538 Confirmed - All Docs" ||
'Status Counts'[TI-Status Combo]="1538 Confirmed - No Docs" ||
'Status Counts'[TI-Status Combo]="1538 Confirmed - On Hold" ||
'Status Counts'[TI-Status Combo]="1538 Confirmed - Some Docs"))
I don't know how to get around using sum for this formula because it is adding together the count column for the desired statuses, which is always 1, so every time I produce a formula that doesn't error out just gets me to an output of 1 (the average in the count column for these statuses). Obviously not the desired output.
Days worked:
Days Worked = [Hours Worked]/8
Hours Worked:
Hours Worked = calculate(sum('Hours Worked'[Regular Hours]),TMD[CommonID])
CommonID is just the unique ID for each person. I do have a date table in my data set that has days, month number, calendar month, calendar year, quarter, etc.
As you could see in the picture I have months in the column of the matrix. What I need the average broken down to is the monthly level but I can't seem to filter the formulas in the right way. I've tried tinkering with the formulas for [Status count per day] and [statuses] but have made no progress. Any help is appreciated.
