Forum Discussion
Helper IIMatrix Column Grand Total to be an Average
Helper IIHi Greg_Deckler - I did go ahead and vote for the enhancement; however, I don't believe it's the same issue I am having. On the below, the total row at the bottom (yellow highlight) is appropriate for my matrix summing the column values. The issue I hve is the area in green highlight where I'm needing these to be averages rather than sums of the row. The % Utilization for the average section would dividing the average total sent by the average total emp count. And the orange highlighted would be sums of the above values like it is in the main table (yellow highlight).
Hi TCatron18,
Thank you for reaching out in Microsoft Community Forum.
you need dynamic DAX measures that detect when they are in the row total context (green cells) and return averages across months
Please follow below steps to resolve the issue;
1. Total Sent (with row total as average):
Total Sent (Avg Row Total) =
IF (
ISINSCOPE('Company'[Name]) && NOT ISINSCOPE('Period Table'[Month]),
AVERAGEX(
VALUES('Period Table'[Month]),
CALCULATE(COUNTROWS('Awards'))
),
COUNTROWS('Awards')
)
2. Total Emp Count (with row total as average):
Total Emp Count (Avg Row Total) =
IF (
ISINSCOPE('Company'[Name]) && NOT ISINSCOPE('Period Table'[Month]),
AVERAGEX(
VALUES('Period Table'[Month]),
VAR MinDate = CALCULATE(FIRSTDATE('Period Table'[Date]))
VAR MaxDate = CALCULATE(LASTDATE('Period Table'[Date]))
RETURN
CALCULATE(
COUNTROWS('Employee Census'),
'Employee Census'[Date] >= MinDate &&
'Employee Census'[Date] <= MaxDate
)
),
VAR MinDate = FIRSTDATE('Period Table'[Date])
VAR MaxDate = LASTDATE('Period Table'[Date])
RETURN
CALCULATE(
COUNTROWS('Employee Census'),
'Employee Census'[Date] >= MinDate &&
'Employee Census'[Date] <= MaxDate
)
)
3. % Utilization (row total as avg sent ÷ avg emp count):
% Utilization (Adjusted Total Row) =
DIVIDE(
[Total Sent (Avg Row Total)],
[Total Emp Count (Avg Row Total)]
)
Please continue using Microsoft Community Forum.
If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.
Regards,
Pavan.