9 Replies Latest reply on Apr 21, 2017 7:35 AM by Prayson Wilfred Daniel

# Calucate based on specifc group of values within a Dimension

Morning,

I am trying to create a calculation in order to only calculate values within a specific team.

Example, trying to calculation only values within the 'Team' dimension and only B and D to receive a total of 26.

I tried to use a Set, but received an error. If [TeamSet] = "True" then [Amount] end

Any thoughts would be greatly appreciated.

Update -

Sorry, I forgot to add an extra element. I need to do a count distinct of IDs associated with those teams as well.

So it'd be an amount of 26 and an ID count total of 5

-Bill

• ###### 1. Re: Calucate based on specifc group of values within a Dimension

Bill,

Is this what you want?

Best,

--Matt

• ###### 2. Re: Calucate based on specifc group of values within a Dimension

This will do the job:

{Exclude Team,[Name]:SUM(IF (Team ='B' OR Team = 'D') THEN ([Amount]) END)}

• ###### 3. Re: Calucate based on specifc group of values within a Dimension

Morning Matt and Prayson,

Thanks for the insight. Please see the updated post, there is another part to it I forgot to include.

- Bill

• ###### 4. Re: Calucate based on specifc group of values within a Dimension

To count Name, assuming that is what you mean by ID,

{Exclude Team,[Name]:COUNTD(IF (Team ='B' OR Team = 'D') THEN Name END)}

There are 4, not 5? or am I missing something?

And good afternoon? It is 15:19 here in Copenhagen, DK,

• ###### 5. Re: Calucate based on specifc group of values within a Dimension

Morning Prayson,

I believe it should be 5.

As Group B includes the Appt Id's of (1, 2, 3, 5) and Group D has (2, 3, 4). So it'd be a distinct count of 5.

Unsure, how you're getting 4. Unless its only counting the distinct id values within team b because of the OR statement

-Bill

• ###### 6. Re: Calucate based on specifc group of values within a Dimension

I am missing ID field in the sample data you provided:

There are only 4 with B and D

• ###### 7. Re: Calucate based on specifc group of values within a Dimension

Ah! I was using your old workbook ... give me a second I got your new workbook

COUNT ID of B and D:

{Exclude Team,[Name],[Appt ID]:COUNTD(IF (Team ='B' OR Team = 'D') THEN ([Appt ID]) END)}

SUM Amount of B and D

{Exclude Team,[Name],[Appt ID]:SUM(IF (Team ='B' OR Team = 'D') THEN ([Amount]) END)}

• ###### 8. Re: Calucate based on specifc group of values within a Dimension

Ah awesome my friend.

Thanks..

Curious, why use Exclude as appose to using Fixed or Include?

-Bill

• ###### 9. Re: Calucate based on specifc group of values within a Dimension

I used exclude because of the level of detail in your worksheet. Exclude is there to ignore partition of data(disaggregation). In SQL it would look like:

SELECT

Sum(Amount) OVER() AS AmountBandD

,Count(Distinct AppID) OVER() AS CountBandD

FROM OurDatasource

WHERE Team IN (B,D);