Total in the second dimension group by first dimension in pivotal table

[#1]

Hi!

I have this dataset:

Group	ID_1	ID_2
A	124	A_124
B	125	A_125
B	126	A_126
C	127	A_127
C	128	A_128
C	129	A_129
C	130	A_130
D	131	A_131

And I would like to make a table like this:

Group	ID_1	ID_2	Volumen
A	124	A_124	1
B	125	A_125	2
	126	A_126
C	127	A_127	4
	128	A_128
	129	A_129
	130	A_130
D	131	A_131	1

But I'm only able to make a table like this:

Group	ID_1	ID_2	Volumen
A	124	A_124	1
B	125	A_125	1
	126	A_126	1
C	127	A_127	1
	128	A_128	1
	129	A_129	1
	130	A_130	1
D	131	A_131	1

Could anyone help me please? I have three dimensions (Group, ID_1, ID_2) and only one expression (count(ID_1), in a pivotal table.

Thanks for your effort!

[MK_QSL]

Use expression

COUNT(DISTINCT TOTAL <Group> ID_2)

[MK_QSL]

You can also create a pivot table

Dimensions

Group

=Aggr(COUNT(DISTINCT TOTAL <Group> ID_2),Group)          //This is a Calculated Dimension

ID_1

ID-2

No Expression

Go to presentation tab

untick Suppress Zero Values

[#4 · Accepted solution]

Another way to do this is to use

aggr(count(DISTINCT TOTAL <Group> ID_2),Group,ID_2)

as a calculated dimension (Volume(. Add it in the second column and you get something that looks like this

[#5]

Ah the supress zero's. Forgot about that little trick

[#6]

Thanks for your answer! I've followed your steps, and the result was:

Group	ID_1	ID_2	=Aggr(COUNT(DISTINCT TOTAL <Group> ID_2),Group)
A	124	A_124	1
B	125	A_125	2
	126	A_126	2
C	127	A_127	4
	128	A_128	4
	129	A_129	4
	130	A_130	4
D	131	A_131	1

It is posible to combine the last column by each group, and for example only have one "2" in the case of the group "B"?

Thanks in advance!

[#7]

Thanks for your answer! It is necessary to have the last column? It is posible to set the Volumen column as the last in the table?

Thanks!

[MK_QSL]

To combine them as a group, you need to use the 2nd method of Calculated Dimension but then it need to be as 2nd column only.

[#9]

Okey, I will do it with de 2nd method! Thanks!

[MK_QSL]

IF you got your answer,pls close the thread by selecting correct/helpful answer.