Set analysis in Chart with special requirement

[tschullo]

I have a bar chart with one dimensions: Audit ID 

For measures, I have:

• an expression for total hours spent for each phase of the audit, i.e. Planning, Fieldwork, Reporting
  • Sum({<Phase={'Planning'}>} [Hrs Spent])
  • Sum({<Phase={'Fieldwork'}>} [Hrs Spent])
  • Sum({<Phase={'Reporting'}>} [Hrs Spent])
• an expression that shows the time remaining on the budget for the project (regardless of phase)

Since more than one team can work on an audit, the issue I'm trying to address is that my current calculation for time remaining is:

[Hrs remaining] - Sum( [Hrs Spent])

This does not account for the fact that if an audit has two teams on it, and the user selects one of those teams, it will calculate the remaining time as if only one team has worked on the audit.  So for example if the budget is 150 hrs, and each tean has worked 50 hrs, there are 50 hours remaining.  But if I select team 1, the remaining comes up as 100 hrs.

To solve this I want to add a new expression that is the time spent by all other teams on this audit (50) and then revise my remaining time formula to be:

[Hrs remaining] - (Sum( [Hrs Spent<by selected team>]) +  Sum( [Hrs Spent]<by other teams>))

My issue is that I can't seem to figure out the set analysis required to produce the time spent by other teams.

Please help!

[rubenmarin · Accepted solution]

Hi you can try with: Sum(TOTAL <[Audit ID]> {<Team=>} [Hrs Spent])-Sum([Hrs Spent]).

- {<Team=>} to ignore selections in team

- TOTAL to ignore Team dimension

This will return the total hours for an auditId, substracting the hours from the team in the dimension should give you the hours dedicated by other teams.

Maybe you nee an If enclosing everything so when you select a tem it doesn't show all the others, not tested, maybe not needed but it can be something like:

If(Count(Team),Sum(TOTAL <[Audit ID]> {<Team=>} [Hrs Spent])-Sum([Hrs Spent]))

[tschullo · Accepted solution]

Thanks! That worked perfectly!

[tschullo]

Ruben,

In my use case, the Team is not part of the chart (it's really a bar chart by Audit)
However if the user selects a team from a filter, the Other Team Time gets set to zero.

Any ideas how to overcome that?

Thanks,

Tony

[rubenmarin]

Hi, have you tried without the count?: Sum(TOTAL <[Audit ID]> {<Team=>} [Hrs Spent])-Sum([Hrs Spent])

[tschullo]

I actually never included the count

[rubenmarin]

I would need a sample to check, I did a simple one and it seems to work but you should have something different.