Forum Discussion

mkwiatkowski's avatar
mkwiatkowski
Frequent Visitor
4 years ago
Solved

Count rows based on column which is a measure

I'm trying to create a count of rows based off a column which is a measure. So in screenshot example below i have a column called 'data_field' and another called 'PercentageCoverage' which is a measure (which looks like this:

 

PercentCoverage = ROUND(SUM(coverage_metrics[records_populated]) / (SUM(coverage_metrics[records_populated]) + SUM(coverage_metrics[records_missing])),2) * 100

 

I would like to be able to count all the values that are:

  • equal to 100
  • between 76-99
  • between 51-75
  • between 26-50
  • between 0-25
 

Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    Set up a new table, e.g. Segments, which contains the min and max values for the range and the text you want to display. Then you can create a new measure

    Num in segment =
SUMX (
    Segment,
    COUNTROWS (
        FILTER (
            'Table',
            [PercentCoverage] >= Segments[Min value]
                && [PercentCoverage] < Segments[Max value]
        )
    )
)

4 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    Set up a new table, e.g. Segments, which contains the min and max values for the range and the text you want to display. Then you can create a new measure

    Num in segment =
SUMX (
    Segment,
    COUNTROWS (
        FILTER (
            'Table',
            [PercentCoverage] >= Segments[Min value]
                && [PercentCoverage] < Segments[Max value]
        )
    )
)
    • mkwiatkowski's avatar
      mkwiatkowski
      Frequent Visitor
      4 years ago

      Thank you, i tried your suggestion but i'm getting rather large results, instead of 30 for the 100% segment i'm getting 88500+. I've included a screenshot of everything i've done. 

       

      As you can see on the table visual on the left the total number of rows is only 63 and the number of rows that have '100 Percent Coverage' is 30 out of the 63 rows in that visual.

       

      • johnt75's avatar
        johnt75
        Super User
        4 years ago

        Can each entry in data_field appear more than once? If so then you can replace the 'coverage_metrics' line with VALUES('coverage_metrics'[data_field]) and that will then only work on the unique values rather than counting duplicates