4 Replies Latest reply on Oct 13, 2017 4:46 AM by Jim Dehner

How to calculate based on Dimension?

This is driving me insane so thanks in advance. I have a very simple table:

So I want to add a calculation: for Non PPD levels, I want to calculate the number of unique donors / 1500, for PPD, want to divide by 500.

Something like this:

{FIXED [Coordinator_Level]: [UniqueDonors]} / 500

Except I want the 500 to be different based on the Coordinator_Level. EVERYTHING I've tried runs up against an error.

Any ideas? I know this is probably simple.

• 1. Re: How to calculate based on Dimension?

Hi Doug

You need to include a qualifier in your equation - 2 ways - one using and IF - Then combination - the other is hardcoding the calc

in the hard coded version try something like {fixed [coodinator_level]='CHICAGO Non PPD] : sum(unique doners)/500  }

You may need to play with the syntax but that is the idea

Using an if works also If [coodinator_level]='CHICAGO Non PPD]  then {fixed ='CHICAGO Non PPD] : sum(unique doners)/500  }

else {fixed ='CHICAGO Non PPD] : sum(unique doners)/1500  } end

Once again you may need to play with syntax

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 2. Re: How to calculate based on Dimension?

If this does n't work, please provide few rows of fake data in Excel.

{FIXED ([Coordinator Level]="Non PPD") :SUM(Unique donors)}/150

and Second =n would be

{FIXED ([Coordinator Level]=" PPD") :SUM(Unique donors)}/50

Thanks

Deepak

• 3. Re: How to calculate based on Dimension?

Thanks for trying but this doesn't work - it doesn't solve the issue of having one calculation column with values that adjust based on one of the row dimensions. Maybe wasn't clear - for each row, I need that column calculation to divide by a different number based on the coordinator value -

• 4. Re: How to calculate based on Dimension?

No that was't clear - at least for me -

the form of the solution will be   {fixed  [coordinator level] :    sum( if contains(min([condinator level]),"PPD" then  sum([unique donors])/ 500        else   sum([unique donors]/1500   end )}   you may need to play with the aggregation issue on the coordinator level and the if statement -

Not clear are the 500 and 1500 the only values and arre they fixed or are they dependent on something else?

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.