Percentage of something based on one criteria

[Anonymous]

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 !

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

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

Table:

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:

Best Regards

Allan

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

[Mariusz]

Hi @Anonymous 

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

[Anonymous]

@Mariusz 

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

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