get count based on 2 columns

[apthansh]

How to get count based on 2 iD's.

Coulm A     Column B

1                        aa

1                        bb

1                        cc

2                        dd

3                         ff

How to get count of columnB for each Id in Column A

Thank you much

[sunny_talwar]

May be add ColumnA as dimension and Count(ColumnB) as expression

[jayanttibhe · Accepted solution]

Sum(aggr(Count(ColumnB),ColumnA))

You can verify it in text Box  if you want to display it outside chart -

Concat(aggr(Count(Distinct [ColumnB]),ColumnA),',')

[apthansh]

Thanks much.That worked.

Just wondering Count instead of Sum should also work right ?

[sudeepkm]

in case you want to show the Column A value along with the count of Column B per Column A  in a text object then below expression can be used.

concat(Aggr([Coulm A]&' - '&count(DISTINCT [Column B]),[Coulm A]),' | ')

output:

1 - 3 | 2 - 1 | 3 - 1

[jayanttibhe]

Yes - it depending on which aggregation function you want to use - Sum / count / min / max etc.. the outer Sum in first example is for summation of the aggregated results. You can play around it.

[apthansh]

Thank you.