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
Moye
Frequent Visitor

Calculate Percentage with Several Filter Arguments

Hi,

New to PowerBI. I'm looking to create a measure that can take 2 counts and display a percentage.

 

I first want to count the number of rows in my table where each column below is equal 100.

[Team Complete (%)]

[Ph0]

[Phase 1-2],

[Ph 3],

[Ph 4a],

[Ph 4b-5],

[Ph 6], [

Ph 6-1]

 

I then want to take that count and divide it by the count of rows of the column below that are equal to 100.

[Team Complete %]

 

 

 

I'm not sure if its possible to put several arguments in a count function, but this is the measure I made:

 

Complete/Phase_Percent = CALCULATE(COUNT('all_projects'[Team Complete (%)]), filter('all_projects','all_projects'[Team Complete (%)]=100 && ('all_projects'[Ph 0]=100 && 'all_projects'[Phase 1-2]=100 &&'all_projects'[Ph 3]=100 && 'all_projects'[Ph 4a]=100 && 'all_projects'[Ph 4b-5]=100 && 'all_projects'[Ph 6]=100 && 'all_projects'[Ph 6_1]=100)))

 

/ CALCULATE(COUNT('all_projects'[Team Complete (%)]), FILTER('all_projects','all_projects'[Team Complete (%)]=100))*100

 

 

 

All numbers are under the datatype of "whole numbers".

 

Any help would be much appreciated.

1 REPLY 1
Eric_Zhang
Employee
Employee

@Moye

That DAX looks good. To have a better readability, you can format it in DAX formatter. You don't have to *100 at the end, just format the measure as percentage. So what is the exact question in your case, to have a better understanding, please post some sample data and the expected output.

Capture.PNG

Complete/Phase_Percent =
CALCULATE (
    COUNT ( 'all_projects'[Team Complete (%)] ),
    FILTER (
        'all_projects',
        'all_projects'[Team Complete (%)] = 100
            && ( 'all_projects'[Ph 0] = 100
            && 'all_projects'[Phase 1-2] = 100
            && 'all_projects'[Ph 3] = 100
            && 'all_projects'[Ph 4a] = 100
            && 'all_projects'[Ph 4b-5] = 100
            && 'all_projects'[Ph 6] = 100
            && 'all_projects'[Ph 6_1] = 100 )
    )
)
    / CALCULATE (
        COUNT ( 'all_projects'[Team Complete (%)] ),
        FILTER ( 'all_projects', 'all_projects'[Team Complete (%)] = 100 )
    )
    

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.