I have a report that calculates average hours worked by person, by month by team. I have a calculated field for “workdays” that I use in the calculation and want to display on the report. I only want to display workdays once per month, not once per team per month, since the number of days in a month does not vary by team. I do not want to move the Teams to rows. How can I suppress the duplicated workdays series?
Here is the table:
Thanks for any help you can provide,
Dave
Hi @dcuozzo,
A standard out-of-box solution allows showing one measure drilled by another dimension while keeping other measures intact.
You might read about that here - Drill into measure by another dimension.
Unfortunately, this would not work in your case, as you want to have these dimensions both on the page filters and the columns.
The workaround is to create a “placeholder member” and modify the measures so that “Avg Hours per Person per Day” only return value for specific teams, while Workdays return value for “placeholder member” and “All Tempo work type” only.
You might achieve that in the following way.
First, create a new “placeholder member” that does not hold any data, but is selectable in the page filter. You might name it “Any team”. The expression for that is as follows.
Aggregate({})
You need to include that member in the page filter selection to display “Team A”, “Team B” and “Any team”.
Then, you might adjust the measures to only display values for the relevant combinations.
The expression for Workdays might be as follows.
CASE WHEN
[Tempo Work Type].CurrentHierarchyMember.Name = [Tempo Work Type].DefaultMember.Name
AND
[Logged by Team].CurrentMember.Name = 'Any team'
THEN
<your original expression for the measure>
END
Further, you might safeguard other measures from possible errors by excluding the “placeholder member” with the following construction.
CASE WHEN
[Logged by Team].CurrentMember.Name <> 'Any team'
THEN
<your original expression for the measure>
END
You may then enable Hide empty->Columns once you have ensured empty results for the irrelevant combinations.
Regards,
Oskars / support@eazyBI.com
This is certainly a creative approach that I would not have thought of. It solves my problem perfectly. Thank you!
