Hi all,
I'm using BusinessObjects BI Platform 4.1 to work on some objects.
I have some data about the sales qty. The user want to create a variable called "Sales Group" base on the qty first and do some further calculation based on this group. The original data looks like:
Store, Color, Qty
1, Red, 200
1, Black, 100
2, Red, 50
2, Black, 100
3, Red, 100
3, Black, 100
[Sales Group] = If [Qty] ForEach ([Store])>200 then "A" else "B"
This code works well and I can get tables below.
Store, Group
1, A
2, B
3, B
But then I met some trouble. So I want to calculate the sum of each group and add the sum into the table like:
Store, Group, Group Sum
1, A, 300
2, B, 350
3, B, 350
I tried both
[Group Sum]=Sum([Qty]) ForEach ([Group]) [Group Sum]=Sum([Qty]) In ([Group])
But both give me a table:
Store, Group, Group Sum
1, A, 300
2, B, 150
3, B, 200
Do anyone has any idea about how to figure this out?
Thanks in advance
Best Regards
use this formula.
=If([Group]="A") Then [Qty] Else [Qty] Where ([Group]="B") In Block
Hi Amit
Thank you for your answer. But the table I wrote in the question is just an example. The real data has more than 300 stores with 10 different Group levels. Do you have any advice for data sets like that?
if you have 10 grpup level then even you can try with multiple if-else condition.
Convert that measure object to dimension , and apply sum of this dim .
Hi Seshu
That's a great answer! Thank you for your help!