Count all values in table that share same value as current row in pivot table

[JohnSonn]

I am trying to count all values in a table that have the same value as the current row.

I have a duplication key and I want to see how many time this key shows up in the rest of the table.

Duplication key:

=CONCATENATE(CONCATENATE([Total Weight kg],"-"),[Total Net Value])

Key Count:

=COUNTAX(ALL('Invoice Data'),CONCATENATE(CONCATENATE([Total Weight kg],"-"),[Total Net Value])=[Duplication Check Key])

But at the moment I am getting this:

410.556-0	11912
3702.224-0	11912
869.4-0	11912
408.29-0	11912
1356.356-0	11912
361.496-0	11912
1773.014-0	11912
1240.89-0	11912

If I do the same thing without the ALL function:

=COUNTAX('Invoice Data',CONCATENATE(CONCATENATE([Total Weight kg],"-"),[Total Net Value])=[Duplication Check Key])

I only count items in the current row and I count all of them.

The difficulty is that you cannot simply use "@"[column] like you can in an excel table. Please someone help

[JohnSonn]

I have a duplication key and I want to see how many time this key shows up in the rest of the table.

Duplication key:

=CONCATENATE(CONCATENATE([Total Weight kg],"-"),[Total Net Value])

Key Count:

=COUNTAX(ALL('Invoice Data'),CONCATENATE(CONCATENATE([Total Weight kg],"-"),[Total Net Value])=[Duplication Check Key])

But at the moment I am getting this:

410.556-0	11912
3702.224-0	11912
869.4-0	11912
408.29-0	11912
1356.356-0	11912
361.496-0	11912
1773.014-0	11912
1240.89-0	11912

If I do the same thing without the ALL function:

=COUNTAX('Invoice Data',CONCATENATE(CONCATENATE([Total Weight kg],"-"),[Total Net Value])=[Duplication Check Key])

I only count items in the current row and I count all of them.

The difficulty is that you cannot simply use "@"[column] like you can in an excel table. Please someone help

[mahoneypat]

If Duplication Key is an existing calculated column, why not just make a table visual with Duplication Key and the following measure? 

RowCount = countrows('Invoice Data')

If the count of rows with same Duplication Key is needed in a calculated column, you could use the following:

RowCount = var currentDupKey = [Duplication Key]

return calculate(countrows('Invoice Data'), all('Invoice Data'), 'Invoice Data'[Duplication Key] = currentDupKey)

[JohnSonn]

@sjoerdvn this still does not work, it reads all of the rows as before.

@mahoneypat  how does the currentDupKey part work? That is what I am trying to achieve.

[JohnSonn]

I could do this using M code in the query editor.

Does anyone know how I could do this?

[JohnSonn · Accepted solution]

 have found a solution to this.

Step 1)

Go into main table query, remove any errors from the net value column (optional)

Create key from relevant columns

Close and load (to data model)

Step 2)

Duplicate this query

Use group by to get a row count using the key as the group category.

Close and load (to data model)

Build a relationship with the original table

Step 3) Use related to get the count column in order to use a 'downstream' variable in any Power Pivot Table (optional)

Thanks for your support peeps!

[mahoneypat]

Glad you figured it out.  You could also do a merge in query editor and bring your count into the first table there, and then diable load on that query so you don't have that extra table in your model.

Regards,

Pat

[JohnSonn]

Thats great advice, thanks Pat

[JohnSonn]

Great thanks for the tip!

[Anonymous]

Again.... Why are you not doing this in Power Query, where such calculations belong?

Best
D

[sjoerdvn]

try:

=COUNTROWS(CALCULATETABLE(ALL('Invoice Data'),VALUES([Total Weight kg]), VALUES([Total Net Value])))

[JohnSonn]

@sjoerdvn Unfortunately this does not work. It seems to be iterating weight and net value so it counts everything

@Anonymous I am trying both in Power Pivot and in the data model in Power Query

[sjoerdvn]

So, is [Duplication Check Key] a measure or a computed column ?

What are the required output columns of your report ?

Anyway, I've done a little test. Assuming the [Key] column is in the required output, you could use:

= COUNTROWS(CALCULATETABLE('Invoice Data',ALL('Invoice Data'),VALUES('Invoice Data'[Total Net Value]),VALUES('Invoice Data'[Total Weight kg])))

[nandukrishnavs]

@JohnSonn 

Did you check DISTINCTCOUNT() of Duplication key?