Forum Discussion
CountIf with Dynamic Reference
Hi! I need to calculate a column in PBI which in Excel would look like this if I were entering the formula in cell C2:
=B2/Countif(A:A,A2)
Pretty simple in Excel. I've gotten this far with the formula in PBI:
Anonymous add new measure "c" with following expression and test
c = DIVIDE( SUM( Table2[b] ), CALCULATE( COUNT( Table2[a] ), ALLEXCEPT( Table2, Table2[a] ) ) )
3 Replies
- parry2k
Super User
Anonymous I never used countif, could you please share exactly what you are trying to do? And also how your tables are related? In your countif example, isn't 2nd parameter should be a criteria instead of single cell.
Hey parry2k -
I'm trying to divide an number by the count of rows with the same reference ID. For example, in the below table the reference ID "ID123" shows up 3 times throughout.
a b c
1 ID123 101
2 ID002 243
3 ID123 101
4 ID123 101
5 ID002 243
6 ID002 243
7 ID002 243
I would like in cell c1 to show 101 / 3. However in cell c2, I would want to see 243 / 4 because the countif will only count rows with an ID that matches the ID in the current row. Hence the cell instead of a hard coded criteria. At least that's how it works in excel. How can I accomplish this in PBI?
Thanks!!
- parry2k
Anonymous add new measure "c" with following expression and test
c = DIVIDE( SUM( Table2[b] ), CALCULATE( COUNT( Table2[a] ), ALLEXCEPT( Table2, Table2[a] ) ) )