Forum Discussion
Count all values in table that share same value as current row in pivot table
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!
Solution Sagetry:
=COUNTROWS(CALCULATETABLE(ALL('Invoice Data'),VALUES([Total Weight kg]), VALUES([Total Net Value])))
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])))