Sum over first category, but show for all categories

[Juliane Behrend]

In a block diagram I would like to show the quotient of two sums of rows of a table over a set of categories , say sum([A])/sum() OVER (All([Axis.X])). The categories are called 'Overall' and then a set of subgroups. Since the subsgroups don't contain all the values of , I would like to use the sum() of the category 'Overall' in the denominator, but then show the result for all categories. How can I solve this

[Juliane Behrend]

I have tried Sum([A]) / Sum(If([Region]="Overall",)), but this is showing the right value for the first [Region] only, while I want to have the quotient for all regions.

[Jaime Serrano Jimenez]

Hi,

Maybe you could include a simple diagram explaining how is your data tableand what you need to accomplish for a better underdtanding about your problem.

Jaime

[Juliane Behrend]

Sensor	Region	Pulses	Signal	sum(Signal)/sum(Pulses_total)
1	Overall	120	2800	210/7800 = 0.027
2	Overall	20	3600	210/7800 = 0.027
3	Overall	70	1400	210/7800 = 0.027
1	Top	120	2800	140/7800 = 0.018
2	Top	20	3600	140/7800 = 0.018
3	Bottom	70	1400	70/7800 = 0.009

 

 Here is an example of what I am trying to do. The signal is suppose to be normalized by the total number of pulses of the region 'Overall' for each region, while the sum(signal) is for each region the sum over the individual measured signals.

 

Oh, and I want to do the calculation in a block diagram, so that filters can be applied, like sensor confirguration or sensor disfunctional, etc.

[Jaime Serrano Jimenez]

Hi,

One option would be:

1. created a calculated column [A]: Sum(If([Region]="Overall",[signal])) in your example this column will have 7800 for all rows

2. Use this column for calculating your column objective:

Sum([Pulses]) over [Region] / [A]

Hope this help.

Jaime

[Juliane Behrend]

Thanks, Jaime, for looking into my problem. This approach is also what I tried initially, but then the numbers don't adjust to filtering. So if, say, sensor 1 had a different configuration than sensor 2 and 3, and I only want to look at the last two, the total number of pulses should also be restricted to those. 

[Juliane Behrend]

I solved this by restructuring the data. Now all regions contain the same amount of rows as 'Overall' and all the pulse data of 'Overall', while the signal data is filled up with zeros. This works, although it is not the most elegant solution because of all the added data.