Forum Discussion
How to Count Duplicated row?(Distinct Column? Index Column?)
Hello.
I am PowerBI User living in Korea.
I solved many problems with your help. Thank you very much.
Data processing is required by utilizing duplicate data.
My current table is as follows.
For data with duplicate case numbers Column, I would like to calculate the number of duplicate columns. (Count of Duplicate Row at Case Number Column)
Using the calculated values, I would like to create a new Hours Duplicate Row column and divide the Actual Hours by the duplicate number of columns.
I've tried a lot of things using the Index Column, but it's hard with my ability.
I need a lot of your advice.
I need your help.
※ The blue painted part is the original Data and the yellow painted part is the result of the column I want.
I think you can do this in Power Query. Select the case number column and choose Group By. Add 2 aggregations, 1 using count rows and 1 using all rows. You can expand the 2nd column to bring back all the data, and then add another new column dividing the actual hours column by the num duplicates column.
Hello DataAnalysis,
Try measure as-
CountofDuplicateRow = CALCULATE(COUNT('Table'[CaseNumber]),ALLEXCEPT('Table','Table'[CaseNumber]))HoursDivideDuplicateRow = Divide(AVERAGE('Table'[ActualHours]),[CountofDuplicateRow])Please accept it as solution if it solves your issue. Kudos are also appreciated.
Cheer,
Shishir
4 Replies
I think you can do this in Power Query. Select the case number column and choose Group By. Add 2 aggregations, 1 using count rows and 1 using all rows. You can expand the 2nd column to bring back all the data, and then add another new column dividing the actual hours column by the num duplicates column.
Hello DataAnalysis,
Try measure as-
CountofDuplicateRow = CALCULATE(COUNT('Table'[CaseNumber]),ALLEXCEPT('Table','Table'[CaseNumber]))HoursDivideDuplicateRow = Divide(AVERAGE('Table'[ActualHours]),[CountofDuplicateRow])Please accept it as solution if it solves your issue. Kudos are also appreciated.
Cheer,
Shishir
CALCULATE (
COUNT ( [CaseNumber] ), FILTER ( TableName, [CaseNumber] = EARLIER ( [CaseNumber] ) ) )Greetings from New Zealand,
I had the exact same question, and solved it for me in a simpler way via a CALCULATED COLUMN:CountofDuplicateRow = CALCULATE(COUNT([CaseNumber]))
