3 Replies Latest reply on Nov 28, 2016 3:25 PM by Shinichiro Murakami

# How to add averages based on groupings of the same measure?

I have a data source with two columns of information, one is the category name and the other is the Measure (a number).

Say I have categories named "A" and "B".  I want to perform the following calculation:

Avg[measure] if category =  "A"  + Avg[measure] if category = "B".

I also have more categories names than just A and B. So I want results for multiple combinations of categories.

A+B

B+C

C+D

A+C

A+D, etc.

What is a good way to set this up?

• ###### 1. Re: How to add averages based on groupings of the same measure?

I don't know your end goal, but does this help?

Edit data source and self join

Then create X-ref table

Thanks,

Shin

• ###### 2. Re: How to add averages based on groupings of the same measure?

Shin,

Thanks for your help and apologies that it took me a while to implement your recommendations.  I was able to do what you suggested and it accomplishes essentially what I desired.

A couple questions if you don't mind.

The resulting cross reference table provides 2 entries for every combination, for example A+B and B+A, is there a method to eliminate the extra answer, B+A?

I thought Filtering may accomplish this and also reduce the table size for just specific combinations, but I can't seem to filter the number of columns and rows and get the intended results.

For example I thought if I filtered the columns on "A" and the rows on "A, "B", "C" and "D", I would see a table with one column, Column "A", and 4 rows "A" through "D", but instead what I get is a table with one entry, column "A" and row "A".

I suppose it is difficult to troubleshoot if I have done something not according to your instructions, but if you can understand my questions above and have a fix I would appreciate any recommendations.

Thanks,

Jon

• ###### 3. Re: How to add averages based on groupings of the same measure?

Hi Jon,

Because table calculation typically has some complicated situations when it's filtered, I recommend to use "Hide".

This dose not violate table relationships, only change the view.

Thanks,

Shin