cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Moye Frequent Visitor
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
Moderator Eric_Zhang
Moderator

Re: Calculate Percentage with Several Filter Arguments

@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
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 274 members 2,796 guests
Please welcome our newest community members: