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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bryanc78
Helper IV
Helper IV

Dax - count number of people below 50%

I would like to count how many people are below 50% when I have a column that is listed below.  As you can see, Bob is at 25% and Lucy is at 66% so the count would be 1.  Similar, a % would be good as well so the answer in this case would be 50%

 

Bob SmithPass
Bob SmithFail
Bob SmithFail
Bob SmithFail
Lucy BrownFail
Lucy BrownPass
Lucy BrownPass
1 ACCEPTED SOLUTION
Cmcmahan
Resident Rockstar
Resident Rockstar

Sure. I'll be making up table/column names, so adjust for your data model:

PeopleOver50% = 
COUNTROWS(
    FILTER(
        SUMMARIZE( Results, Results[Person], 
            "Count Pass", CALCULATE(COUNTROWS(Results), Results[Pass/Fail] = "Pass"), 
            "Count Total", COUNTROWS(Results)
        ), 
        [Count Pass]/[Count Total] >= .5
    )
)

 

This measure will give you a direct count of those people with a Pass/Total of greater than or equal to 50%.  I kept all the parts of the percentage calculation separated to allow you to rearrange and use the pieces as needed.  Usually I would calculate the percentage directly instead of storing them separately in the SUMMARIZE table.

 

 

 

View solution in original post

4 REPLIES 4
Cmcmahan
Resident Rockstar
Resident Rockstar

Sure. I'll be making up table/column names, so adjust for your data model:

PeopleOver50% = 
COUNTROWS(
    FILTER(
        SUMMARIZE( Results, Results[Person], 
            "Count Pass", CALCULATE(COUNTROWS(Results), Results[Pass/Fail] = "Pass"), 
            "Count Total", COUNTROWS(Results)
        ), 
        [Count Pass]/[Count Total] >= .5
    )
)

 

This measure will give you a direct count of those people with a Pass/Total of greater than or equal to 50%.  I kept all the parts of the percentage calculation separated to allow you to rearrange and use the pieces as needed.  Usually I would calculate the percentage directly instead of storing them separately in the SUMMARIZE table.

 

 

 

I got it to work. I actually needed those 50% and below so I was updating it to <= .50 but it didn't work. Keeping it your way and just changing the Pass to Fail worked. Thank you

Which part to I remove to only show a count?

You should use that entire expression to show a count.  Add that measure into a card or other visual, and it will display a number of people that have a pass rate of over 50%.  

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.