Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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:

 

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?
Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    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's avatar
    parry2k
    Super User
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        parry2k
        Super User
        7 years ago

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

         

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