cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Percentage of something based on one criteria

Hello everyone ! 

 

I am a new Power BI user. I read the microsoft help, the microsoft guide, I watched some videos on Youtube about Power BI and I feel like I understood a lot of things about Power BI. But... When I try to do a measure, it doesn't work.

 

I explain my situation :

I have only 1 table which is called "Tâches".

On this table, there is a column called "Compartiment" in which there is different lines ("Bench", "Candidature mission", "Staffing/Besoins", "Recrutements", "Sous Traitants", "Formations"). 

There is another column called "Progression" in which there is 3 posible informations : "Terminées", "En cours", "Non commencées". In english, it means Finished, In progress and Not started.

Here is a picture of those informations : https://ibb.co/thnMx87 

 

My goal with those informations is to count the number of "Bench" (column "Compartiment") which are "En cours" (Colum "Progression")  and divide it by the Total number of Bench.

I tried a lot of things, but I doesn't work. For example, the last things I tried are :

 

% Bench En cours = DIVIDE(COUNTAX('Tâches',"Bench"&"En cours"),COUNTAX('Tâches',"Bench"))  

Or

Percentage = COUNTROWS(FILTER('Tâches', IF(CONTAINS('Tâches','Tâches'[Compartiment],"Bench") && IF(CONTAINS('Tâches','Tâches'[Progression],"En cours")))))

 

I'm also unable to count the number of "Bench" and the number of "Bench" which are "En cours".  

 

Thanks for advance for your help !

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Percentage of something based on one criteria

Hi, @Jdubois 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

 

Table:

a1.png

 

You may create a measure as below.

Percentage = 
DIVIDE(
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[Compartiment]="Bench"&&
            'Table'[Progression]="En cours"
        )
    ),
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[Compartiment]="Bench"
        )
    )
)

 

Result:

a2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Highlighted
Super User V
Super User V

Re: Percentage of something based on one criteria

Hi @Jdubois 

 

Can you create a sample data and paste into the body of the post so we can use it to create a solution, please can you provide an expected outcome.

btw. your link does not work.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Highlighted
Regular Visitor

Re: Percentage of something based on one criteria

@Mariusz 

Jdubois_0-1594285446462.png

Here is the first image, I don't know why the link didn't work

 

Jdubois_1-1594285576799.png

Here is the sample database, i'm sorry, that's not interesting.. My data is coming from Office 365 Planner and I have only 1 Table. In general, I want to create my measures by filtering with the text on the "Compartiment" column, but i'm unable to do this..

 

Other question.. I spent a lot of time searching and trying a lot of solutions, but I felt like it was imposible to automate data actualization with planner data. So, I transfer them via Excel. So, if at the end i'm able to do my measures, will the measures be actualized if I import the new actualized excel file (imported from the actualized Planner Data).

 

Thanks for advance

Highlighted
Community Support
Community Support

Re: Percentage of something based on one criteria

Hi, @Jdubois 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

 

Table:

a1.png

 

You may create a measure as below.

Percentage = 
DIVIDE(
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[Compartiment]="Bench"&&
            'Table'[Progression]="En cours"
        )
    ),
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[Compartiment]="Bench"
        )
    )
)

 

Result:

a2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors