Counting Pairs and displaying value next to them

[Anonymous]

I want to count how many times a specific pair of values occur and display the count next to them. How would I count a set of values and duplicate the count x number of times to line up with original value?

For this table the pairs would be in plant and type. So 'tree' & 'seed' show up 2 times, 'bush' & 'leaf' 3 times etc. How would I count that and then put a 2 next to each instance of tree and seed, and a 3 next to bush and leaf? Could I possibly make a new table with duplicates removed and then just count the pairs and put the total in the corresponding column? I want to be able to update the data and have it automatically recount the pairs so I don't know if making new tables is the best idea.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Plant]=EARLIER(Table1[Plant])&&Table1[Type]=EARLIER(Table1[Type])))

Hope this helps.

[ChrisMendoza]

@Anonymous -
You can do as a column:

Column = 
VAR __pair = TableName[Plant] & TableName[Type]
VAR Result =
CALCULATE(
    COUNTROWS(TableName),
    TableName[Plant] & TableName[Type] = __pair
)
RETURN Result

or as a measure:

Measure = 
VAR __pair = SELECTEDVALUE(TableName[Plant]) & SELECTEDVALUE(TableName[Type])
VAR Result =
CALCULATE(
    COUNTROWS(TableName),
    TableName[Plant] & TableName[Type] = __pair
)
RETURN Result

[Anonymous]

I have a lot more columns than what is shown. When I use the column method it shows 1's next to every row, but when that column is put into a table with just the pair coulmns it shows a total, but that total has some wrong numbers. Some of the counts for the pairs are right, but some are too high.  I think that having extra columns that can vary with the pair may mess up the new column method.