
1. Re: Sum accross multiple dimensions in Table
swaroop.gantela Jul 12, 2016 8:06 PM (in response to Béatrice LORIOT)Beatrice,
Please see if the attached could be a first step for you.
I used Level of Detail calculations to get the counts.
For Rate 1:
{ FIXED [Source]:SUM(IF [Rate]=1 THEN 1 ELSE 0 END )}
Meaning if anywhere in a particular Source there is a Rate1, count it
and fix the sum for the whole source.
I also used that count in getting the Amount sum:
IF [CountRate1]>0
THEN { FIXED [Source]:SUM([Amount])}
ELSE 0
END
Using this scheme, I needed to make a separate
calculation for each combination.
To get the Count for Rate1&3:
IF [CountRate1]>0 AND [CountRate3]>0
THEN { FIXED [Source]:COUNT([Rate])}
ELSE 0
END

210967group.twbx 16.3 KB


2. Re: Sum accross multiple dimensions in Table
Béatrice LORIOT Jul 13, 2016 1:01 AM (in response to swaroop.gantela)Hi Swaroop,
Thanks a lot for your help !
The count works well !!
but I don't get the correct Amount sum with the formula proposed and I am not sure what the amount I get relates to...

3. Re: Sum accross multiple dimensions in Table
Béatrice LORIOT Jul 13, 2016 9:20 AM (in response to Béatrice LORIOT)Hello Again,
Actually, the formula for the count is not working. It gets a correct total but not a correct total by line (source).
I changed to
SUM(IF [Rate]=1 THEN 1 ELSE 0 END ) and now it's working.
What I still can't get is the amount.
Thanks a lot

4. Re: Sum accross multiple dimensions in Table
swaroop.gantela Jul 13, 2016 10:01 AM (in response to Béatrice LORIOT)1 of 1 people found this helpfulBeatrice,
Would need some clarification of what you seek for the Amount.
For the Rank 1 Amount, are you seeking:
any source that has a Rank 1, add up all of their Amounts
(regardless of the Rank).
So in your above example, Source 1 does have amongst
its entries a Rank1, so would you want to sum the amounts of
Lines 1,3, and 7 (even though line 7 has a rank of 3)
which would total 800, as you have in the example?
And then for Rank 1,3 Amount,
add up only the amounts for those Sources that have both
a Rank1 and a Rank3?

5. Re: Sum accross multiple dimensions in Table
Béatrice LORIOT Jul 13, 2016 10:36 AM (in response to swaroop.gantela) 
6. Re: Sum accross multiple dimensions in Table
swaroop.gantela Jul 13, 2016 11:23 AM (in response to Béatrice LORIOT)Beatrice,
That does make the sums more straightforward.
Should be just like the counts:
SUM(IF [Rate]=1 THEN [Amount] ELSE 0 END)
And for 1 and 3:
SUM(IF ([Rate]=1 OR [Rate]=3) THEN [Amount] ELSE 0 END)

210967group2.twbx 18.3 KB


7. Re: Sum accross multiple dimensions in Table
Béatrice LORIOT Jul 14, 2016 7:46 AM (in response to swaroop.gantela)Thanks Swaroop, that works perfectly fine !
Have a great day,
Kind regards

8. Re: Sum accross multiple dimensions in Table
swaroop.gantela Jul 14, 2016 8:00 AM (in response to Béatrice LORIOT)Beatrice,
Glad it worked for you.
All the best.