CountIf with Dynamic Reference

[Anonymous]

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:

ShipmentCost = CALCULATE(

COUNT(ShipmentLevel[Shipment Gid]),

FILTER(ShipmentLevel,

Where 'ShipmentLevel' is the table and [Shipment Gid] is the id I want to reference dynamically.

But I can't figure out how to input a dynamic reference based on the row being calculated. AND I can't figure out how to reference what would be cell "b2" in my excel example above.

Any help here?

[parry2k]

@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.

[Anonymous]

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 · Accepted solution]

@Anonymous add new measure "c" with following expression and test

c = DIVIDE( SUM( Table2[b] ), CALCULATE( COUNT( Table2[a] ), ALLEXCEPT( Table2, Table2[a] ) ) )