Count Values Based on Duplicate Values

[robertsb41]

I have two columns, Contract ID & Bid Rank.  I want to count the number of bid rank for each unique contract ID.  So for 180119-B03 the result would be 7 and for 180119-C01 the result would be 6.  Thanks!

Contract ID	Bid Rank
180119-B03	1
180119-B03	2
180119-B03	3
180119-B03	4
180119-B03	5
180119-B03	6
180119-B03	7
180119-C01	1
180119-C01	2
180119-C01	3
180119-C01	4
180119-C01	5
180119-C01	6

[Taoufiq_Zarra]

if you want to keep Table detail :

Solution :

Data:

LOAD * INLINE [
    Contract ID, Bid Rank
    180119-B03, 1
    180119-B03, 2
    180119-B03, 3
    180119-B03, 4
    180119-B03, 5
    180119-B03, 6
    180119-B03, 7
    180119-C01, 1
    180119-C01, 2
    180119-C01, 3
    180119-C01, 4
    180119-C01, 5
    180119-C01, 6
];

left join 

load [Contract ID], count([Bid Rank]) as Count_BidRank resident Data group by [Contract ID];

output :

or without detail :

Data:

LOAD * INLINE [
    Contract ID, Bid Rank
    180119-B03, 1
    180119-B03, 2
    180119-B03, 3
    180119-B03, 4
    180119-B03, 5
    180119-B03, 6
    180119-B03, 7
    180119-C01, 1
    180119-C01, 2
    180119-C01, 3
    180119-C01, 4
    180119-C01, 5
    180119-C01, 6
];

output:
noconcatenate

load [Contract ID], count([Bid Rank]) as Count_BidRank resident Data group by [Contract ID];

drop table Data;

output :

[robertsb41]

Thank you for your reply but I am just looking for an expression for a KPI.  I should have been more clear.  Is that possible?

[Taoufiq_Zarra · Accepted solution]

you can add

Contract ID as dimension and =count([Bid Rank]) as Expression

output :

or kpi for each Contract ID

=count({<[Contract ID]={'180119-B03'}>}[Bid Rank])

output :