Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mkwiatkowski
Frequent Visitor

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
 

dfrSm.png

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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]
        )
    )
)

View solution in original post

4 REPLIES 4
johnt75
Super User
Super User

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]
        )
    )
)

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.

 

2022-05-25_10-29-40.jpg

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

Brilliant! That fixed it. Thank you so much for your help 🙂

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.