Calculate sum issue : X projects = 0 charges

[Lotam]

Good morning !

I work on 800 projects that are charged with FTE (full time equivalent) by managers and dispatched between 50 files (for the 50 managers).

Some projects have 0 FTE as a total, while some projects have some contributions by some manager but not all of them.

I would like to calculate the number of missing contribution by managers.

For exemple : - Project 1 = 5 missings contributions. Indicates that the teams are the following (X1 ; X2 ; ...)

I have tried to do it in the following file, first page "Contributeurs manquants par Entités" with the formula :

"N-projets non-chargés = CALCULATE(COUNT('Charges'[N_Projet]), FILTER(Charges,[SOMETPS22020]=0))"

But what I get are a huge amount of projects, that are not correct. I fear that it is counting the number of 0 in my core files where the 0 appears to a maximum of 66 times per projects because it is expressed per months (66 months), and I want to know if a project has 0 charge as a total of those 66 months.

Here is the link to my file : https://www.swisstransfer.com/d/ac8d5573-55a0-402d-8ce4-9d6ddaccb21e 

Please do tell me if I was not clear enough or need to imput more information. I'm continuing to search for a solution and will put the solution if I finaly find it elswhere.

Thank you very much for your appreciated help !

Best regards,
Marc

[AlB · Accepted solution]

@Lotam

Try this new version. Something complicated was going on. The result is now 4 for the mentioned case:

N-projets non-chargés V4 =
COUNTROWS (
    FILTER (
        CALCULATETABLE (
            DISTINCT ( 'Charges'[N_Projet] ),
            Charges[Itération_PdC] = "S2-2020"
        ),
        CALCULATE ( SUM ( Charges[Charges] ) = 0 )
    )
)

Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.

Contact me privately for assistance with any large-scale BI needs, tutoring, etc.

Bless you

[amitchandak]

@Lotam , Try a measure like

countx(filter(summarize('Charges','Charges'[N_Projet],"_1",[SOMETPS22020]),[_1]=0),[N_Projet])
or
countx(filter(summarize('Charges','Charges'[N_Projet],"_1",[SOMETPS22020]+1),[_1]=0),[N_Projet])

[Lotam]

Hello @amitchandak ,

Thank you for your answer.

I tried both calculation, but It did not fit what it should look like. As I have a great difference between what PBI shows me and the reality (160 projects with 0 FTE, but my files will show me 11).

Still, thank you very much, I'll keep looking and using your calculation to try to find my answer.

Crowd

[AlB]

Hi @Lotam 

What is the expected result? I don't completely understand the logic for the calculation but try this:

N-projets non-chargés V3 = COUNTROWS(FILTER(DISTINCT('Charges'[N_Projet]), [SOMETPS22020]=0))

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Lotam]

I think I found out but I am unsure on how to correctly do it in the formula.

PowerBI may not being rounding up some numbers. The total may be of 0,4 and PowerBI counts it as a 0.

I don't seem to be able to use the Roundup formula correctly (if it is this one).

Plus, I also think PowerBI counts my first row (which is the headtitle of my board in excel) as one project being 0.

Do you know how to counter this?

Thank you,
Crowd

[Lotam]

It seems I keep trying to answer you and the forum denies it !

Hello @AlB ,

Thank you for your answer.

I am trying to count the number of projects with 0 FTE so I can have a good picture of the amount of work for each manager that still has to be done.

Your solution works, but not perfectly as I have a difference of 2 or 3 in my results :
Expected 16

PowerBI : 18

Does that mean that Countrows is counting additionnal rows? But I can't seem to find out where or why. I will keep looking.
If you have the answer, please do enlight me !

Thank you again,

Crowd

[AlB]

@Lotam 

Where/how are you using the my measure? If i place it in a card visual in the file you shared I get a result of 406. Where do you get the 18?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Lotam]

Sorry, if its in a card its indeed 406 as a total.

18 was from one of the files out of 50 (easier to count).

Your answer is the best one I have, even if its not 100% accurate.
I still don't know why its not. Maybe it should be a Distinctcount?

Regards,

Crowd

[AlB]

@Lotam 

What file are you looking at? Either send me a pbix with the scenario you have now (with 18 as result) or tell me how to build it with the file you shared earlier. Then I can have a look.

By the way why are some of the numbers on the "Itération_PdC" so small?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Lotam]

@AlB

For example, in the PBI file, I have in "Contributeurs manquants par Entités" a blue square called "DEP" with a display in ring in it. There is "PLM" in it and when over it it displays 7.

In the Excel file, it is "DEP_DPI_PLM". I have put a sum of each rows at the end of them. I only have 4 with a total of 0 (in red).

So there is a discrepancy of 3.

Some numbers in the "Itération_PdC" are very small because of the way I need it to be input (for further extraction for entities of my employer).

Gratefuly

Crowd

https://www.swisstransfer.com/d/c3829a86-7617-48f2-8362-d64613c073e3

[AlB · Accepted solution]

@Lotam

Try this new version. Something complicated was going on. The result is now 4 for the mentioned case:

N-projets non-chargés V4 =
COUNTROWS (
    FILTER (
        CALCULATETABLE (
            DISTINCT ( 'Charges'[N_Projet] ),
            Charges[Itération_PdC] = "S2-2020"
        ),
        CALCULATE ( SUM ( Charges[Charges] ) = 0 )
    )
)

Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.

Contact me privately for assistance with any large-scale BI needs, tutoring, etc.

Bless you

[Lotam]

Thank you very much @AlB !

This does it very nicely. With your solution, a collegue of mine and I tried to complete it just a bit with a visualisation to sum up the numbers of rows with 0 charges.

For example, for each boxes (DEP, MEI, PPI, MSP and DIR) have the total of all rows with 0.
DEP should be 26+15+11+6+4+4+2+1 =68.

Unfortunatly we stalled while trying the following :

N-unsered projects V5

COUNTROWS (

FILTER(

IF(AND (Charges[Itération_PdC] = "S2-2020", Charges[Groupe_Contributeur]="IST"

),(CALCULATETABLE (

DISTINCT ( 'Charges'[N_Projet] )))),

CALCULATE ( SUM ( Charges[Charges] ) = 0 )

)

)

Would you have an idea?

Again thank you, I have marked the post as having being solved !

Best regards,
Crowd

[AlB · Accepted solution]

@Lotam

I'm sorry I couldn't answer sooner. Try this new measure that uses the one we already had:

N-projets non-chargés V4_TOT =
SUMX ( DISTINCT ( Charges[Groupe_Contributeur] ), [N-projets non-chargés V4] )

Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.

Contact me privately for assistance with any large-scale BI needs, tutoring, etc.

Bless you

[Lotam]

@AlB Thank you again, my colleague and I were dumbfounded by the simplicity ! It works like a charm.

Thank you again !

Regards, Lotam